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';
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.

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

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.

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
    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;

  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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.