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 