Author: way0utwest

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.