Finding Objects in a Schema #SQLNewblogger

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.

2018-09-17 19_25_07-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

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:

2018-09-17 19_26_25-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft

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';
        o.type_desc, AS 'Schema Name', AS 'Object Name',
FROM sys.objects o
     INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE = @schema;

I can execute that and I’ll see the objects I need.

2018-09-17 19_28_57-SQLQuery1.sql - dkrSpectre_sql2017.sandbox (DKRSPECTRE_way0u (68))_ - Microsoft


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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

5 Responses to Finding Objects in a Schema #SQLNewblogger

  1. Hans Jørgen Pedersen says:

    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

    SET @SQL = ‘
    USE ?

    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 )
    DB_NAME() AS [Database],
    o.type_desc, AS ”Schema Name”, AS ”Object NAME”,

    FROM sys.objects o
    INNER JOIN sys.schemas s ON s.schema_id = o.schema_id

    WHERE = ”’ + @schema + ”’

    IF @@ROWCOUNT > 1 (SELECT * FROM @Result)


    — Execute against all DBs
    EXECUTE dbo.sp_msforeachdb @command1 = @SQL;


  2. way0utwest says:

    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?


    • Hans Jørgen Pedersen says:

      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.


  3. way0utwest says:

    Interesting. That would probably make a great series of blogs or articles if you wanted to write about it.


    • Hans Jørgen Pedersen says:

      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.


Comments are closed.