SQL Server – Return SCHEMA for sysobjects

How to I get the SCHEMA when doing a select on sysobjects?

I am modifing a stored procedure called SearchObjectsForText which returns only the Name but I would also like to include the SCHEMA.

  • Bit-flipping operations in T-SQL
  • MSSQL Regular expression
  • Eliminating NULLs when using CASE in SQL Server SELECT statement
  • How can I do this Spatial Query in Sql 2008?
  • Combine Column and Line Chart Report Builder 3.0
  • Deadlock under ReadCommited IL
  • Right now it is doing something similar to this:

    FROM sysobjects

    I would like to know what tables need to be joined to return the SCHEME for each ‘name’.

    7 Solutions collect form web for “SQL Server – Return SCHEMA for sysobjects”

    If you mean SQL Server 2005 or higher, use sys.objects instead of sysobjects:

    SELECT  sys.objects.name, sys.schemas.name AS schema_name
    FROM    sys.objects 
    INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

    2005 introduced schemas. up to 2000, users equaled schemas. The same query for SQL Server 2000:

    SELECT  sysusers.name AS OwnerName, sysobjects.name
    FROM sysobjects
    INNER JOIN sysusers ON sysobjects.uid = sysusers.uid

    On Sql Server 2005 (and above) you can use the sys.objects view:

      name                    as  ObjectName,     
      schema_Name(schema_id)  as  SchemaName

    In Sql Server 2000 (and below), “schema” had a different conceptual meaning. Note from MSDN:

    In earlier releases of SQL Server, databases could contain an entity called a “schema”, but that entity was effectively a database user. SQL Server 2005 is the first release of SQL Server in which a schema is both a container and a namespace.

    Could you use the Information_Schema view(s) instead?

    SELECT DISTINCT table_name, table_schema

    According to the MSDN page (for SQL Server 2008 and above),

    Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.

    However, it seems that they’re probably referring to an issue where you have a table name and are trying to find its schema, which wouldn’t work if there were multiple tables with the same name (in different schemas). If you’re querying for multiple results (not just trying to find the schema for a specific table), then it should be fine.

    I would favor using the more focused “sys” views – sys.procedures instead of sys.objects. You’ll need to join it with the sys.schemas view to get schema name and such.

        s.name 'Schema',
        p.type_desc, p.create_date, p.modify_date
        sys.procedures p
    inner join
        sys.schemas s ON p.schema_id = s.schema_id

    I would start to get away from using “sysobjects” since Microsoft clearly states in Books Online that “sysobjects” is subject to removal in a future release:

    This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


    Just to repeat what’s already been suggested here, here’s what I’ve used, to get a list of Tables, Stored Procedures, Views and Functions in my database:

    SELECT schema_Name(schema_id)  as  SchemaName,
           [name],              --  Name of the Table, Stored Procedure or Function
           [type]               --  'V' for Views, 'U' for Table, 'P' for Stored Procedure, 'FN' for function
    FROM sys.objects 
    AND [name] NOT LIKE 'sp_%'
    AND [name] NOT LIKE 'fn_%'
    ORDER BY 3 DESC,        --  type first
            1 ASC,          --  then schema
            2 ASC           --  then function/table name

    …and here’s what our good friend Northwind would return…

    enter image description here

    In SQL 200:

    select DISTINCT
      name            as  ObjectName,     
      USER_NAME(uid)  as  SchemaName

    In earlier releases of SQL Server, databases could contain an entity called a “schema”, but that entity was effectively a database user.

    Have included an option to delete all objects starting with certain prefix and optionally from certain schema.
    By the way, I added extra query to get all types which are not stored on sysobjects by default.

    I have uploaded entire sample script to GitHub:

    Part 1: Temporary Stored Procedure:

    IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
        DROP PROCEDURE _temp_DropAllDnnObjects;
    CREATE PROCEDURE _temp_DropAllDnnObjects
        @object_prefix NVARCHAR(30),
        @schema_name sysname = NULL
        DECLARE @sname sysname, @name sysname, @type NVARCHAR(30)
        DECLARE @object_type NVARCHAR(255), @sql NVARCHAR(2000), @count INT = 0
            SELECT sname, [name], xtype 
            FROM (
                SELECT SCHEMA_NAME(schema_id) as sname, [name], [type] as xtype
                    FROM sys.objects
                    WHERE [type] IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')
                        AND name LIKE @object_prefix + '%'
                        AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
                UNION ALL
                SELECT SCHEMA_NAME(schema_id) as sname, [name], 'TYPE' as xtype
                    FROM sys.types
                    WHERE is_user_defined = 1
                        AND [name] LIKE @object_prefix + '%'
                        AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
                ) a
            ORDER BY CASE xtype
                            WHEN 'P'    THEN 1
                            WHEN 'FN'   THEN 2
                            WHEN 'IF'   THEN 3
                            WHEN 'TF'   THEN 4
                            WHEN 'TR'   THEN 5
                            WHEN 'V'    THEN 6
                            WHEN 'U'    THEN 7
                            WHEN 'TYPE' THEN 8
                            ELSE 9
                        END, name
        OPEN curs;
        FETCH NEXT FROM curs INTO @sname, @name, @type;
        WHILE @@FETCH_STATUS = 0
            SET @count = @count + 1
            -- Configuration point 2
            SET @object_type = CASE @type
                            WHEN 'P'    THEN 'PROCEDURE'
                            WHEN 'FN'   THEN 'FUNCTION'
                            WHEN 'IF'   THEN 'FUNCTION'
                            WHEN 'TF'   THEN 'FUNCTION'
                            WHEN 'TR'   THEN 'TRIGGER'
                            WHEN 'V'    THEN 'VIEW'
                            WHEN 'U'    THEN 'TABLE'
                            WHEN 'TYPE' THEN 'TYPE'
                            '<TYPE>', @object_type),
                            '<SCHEMA>', @sname),
                            '<NAME>', @name)
            BEGIN TRY  
                PRINT @sql
            END TRY  
            BEGIN CATCH  
                PRINT 'ERROR: ' + ERROR_MESSAGE()
            END CATCH  
            FETCH NEXT FROM curs INTO @sname, @name, @type;
        PRINT CONCAT('Objects Found: ', @Count)
        PRINT ''
        PRINT '------------------------------------------------------'
        PRINT ''
        CLOSE curs;
        DEALLOCATE curs;
        RETURN @Count

    It will continue on errors (and display the error message). It will return a count of all objects found.

    Part 2: Call Stored Procedure with parameters:

    You can create a WHILE loop in order to run the command until no object is left (dependencies), as follows:

    DECLARE @count INT = 1
    WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'dnn';
    SET @count = 1
    WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'aspnet';
    SET @count = 1
    WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'vw_aspnet';

    Part 3: Finally, get rid of the procedure:

    IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
        DROP PROCEDURE _temp_DropAllDnnObjects;
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.