Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
One of the things I needed to do recently was move some objects from one schema to another. I wrote about moving an object between schemas recently, but another part of that process was finding the objects to move.
This is a quick post on how to find the objects in a schema. To start, here are a number of objects in a test database.
A schema has a name, which is the way that we would search for related objects. That means I want a parameter for my query, so I’ll start with a variable to store the name. For me, I’ll use a well named variable like this:
DECLARE @schema VARCHAR(100) = 'SallyDev';
Now I have a schema name, where do I find schema data? There is a DMV called sys.schemas, which contains a bit of meta data. If I query that, I see this:
I can see my SallyDev schema, so I know I’ll query this DMV.
The other information I need is the object data, which is in sys.objects. I query that for the various data I want, but I want to limit data by the schema. In sys.objects, there is a schema_id, which is the data I’ll join with from sys.schemas.
When I do that, I build a query like this:
DECLARE @schema VARCHAR(100) = 'SallyDev';
SELECT
o.type_desc,
s.name AS 'Schema Name',
o.name AS 'Object Name',
o.object_id
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name = @schema;
I can execute that and I’ll see the objects I need.
SQLNewBlogger
This was a post related to the one on moving objects and I wrote this write after that one. It was only about 5 minutes longer to put this together, and it gives me a script I can easily search for on my blog if I need to do this task.
Once again, a quick and easy way to show some skills, practice explaining something, and get some knowledge stored for my own reference.
Voice of the DBA 
Thanks. But I have more use of a script which crawls all the databases on my instance.
So I took the inspiration and expanded it a little:
— Find objects belonging to a specific schema – across all databases
DECLARE @schema VARCHAR(100) = ‘SallyDev’;
— Entire instance
DECLARE
@SQL NVARCHAR(MAX)
SET @SQL = ‘
USE ?
BEGIN
DECLARE @Result table ( [Database] nvarchar(128), [type_desc] nvarchar(60), [Schema Name] nvarchar(128), [Object NAME] nvarchar(128), [object_id] int )
INSERT INTO @Result ( [Database], type_desc, [Schema Name], [Object NAME], object_id )
SELECT
DB_NAME() AS [Database],
o.type_desc,
s.name AS ”Schema Name”,
o.name AS ”Object NAME”,
o.object_id
FROM sys.objects o
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE s.name = ”’ + @schema + ”’
IF @@ROWCOUNT > 1 (SELECT * FROM @Result)
END’
— Execute against all DBs
EXECUTE dbo.sp_msforeachdb @command1 = @SQL;
LikeLike
Interesting. Is this because you have a number of copies of the same database for different clients/uses? Or you use the same schema over and over for different databases?
LikeLike
I could have copies, but no.
I use schema more for ordering/naming purposes than for security reasons. And I split things across databases based on their nature (example: staging tables in one, analytical results in another, utility procedures in a third) – and then I use schema to weave them together: All things connected to a project gets the same schema with a project “name” – Then it is far easier to clean up in the databases afterwards – and I still get to collect reusable stuff in plain sight across work tasks.
LikeLike
Interesting. That would probably make a great series of blogs or articles if you wanted to write about it.
LikeLike
Yeah, I guess you are right. I have spent considerable time over the years trying to understand the “original” purpose of “schema” – and have kinda developed my ideas from various sources. But I am still far from settled in my ways. Maybe I never will be. So it is one of those things that I would want to do “eventually”: I like your idea of putting the interim considerations in a blog, but I don’t find the time for getting into this technique and use the old fashioned Word/Script way… 🙂
Btw. I just realized a typo in my script above: I only show @Result if @@ROWCOUNT > 1. It should say “> 0” of course.
LikeLike