tablas - SQL Server-Devolver SCHEMA para sysobjects

tablas sys sql server

Question

¿Cómo obtengo el SCHEMA cuando hago una selección en sysobjects?

Estoy modificando un procedimiento almacenado llamado SearchObjectsForText que devuelve solo el nombre, pero también me gustaría incluir el SCHEMA.

En este momento está haciendo algo similar a esto:

SELECT DISTINCT name
FROM sysobjects

Me gustaría saber qué tablas deben unirse para devolver el esquema para cada ''nombre''.

Answer 1

¿Podría usar la (s) vista (s) información (s) del esquema?

SELECT DISTINCT table_name, table_schema
FROM INFORMATION_SCHEMA.TABLES

Según la página de MSDN (para SQL Server 2008 y superior),

No utilice las vistas INFORMATION_SCHEMA para determinar el esquema de un objeto. La única forma confiable de encontrar el esquema de un objeto es consultar la vista de catálogo sys.objects.

Sin embargo, parece que probablemente se refieren a un problema en el que tiene un nombre de tabla y está tratando de encontrar su esquema, que no funcionaría si hubiera varias tablas con el mismo nombre (en diferentes esquemas). Si está buscando resultados múltiples (no solo tratando de encontrar el esquema para una tabla específica), entonces debería estar bien.

Answer 2

En SQL 200:

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

En versiones anteriores de SQL Server, las bases de datos podían contener una entidad llamada "esquema", pero esa entidad era efectivamente un usuario de la base de datos.

Answer 3

En Sql Server 2005 (y superior) puede usar la vista sys.objects:

select 
  name                    as  ObjectName,     
  schema_Name(schema_id)  as  SchemaName
from 
  sys.objects

En Sql Server 2000 (y más abajo), "esquema" tenía un significado conceptual diferente. Nota de MSDN:

En versiones anteriores de SQL Server, las bases de datos podían contener una entidad llamada "esquema", pero esa entidad era efectivamente un usuario de la base de datos. SQL Server 2005 es la primera versión de SQL Server en la que un esquema es tanto un contenedor como un espacio de nombres.

Answer 4

Han incluido una opción para eliminar todos los objetos que comienzan con cierto prefijo y, opcionalmente, de cierto esquema. Por cierto, agregué consulta adicional para obtener todos los tipos que no están almacenados en sysobjects por defecto.

He cargado un script de muestra completo en GitHub: DropAll_Dnn_Objects.sql

Parte 1: Procedimiento de almacenamiento temporal:

IF OBJECT_ID(''_temp_DropAllDnnObjects'') IS NOT NULL
    DROP PROCEDURE _temp_DropAllDnnObjects;
GO

CREATE PROCEDURE _temp_DropAllDnnObjects
    @object_prefix NVARCHAR(30),
    @schema_name sysname = NULL
AS
BEGIN
    DECLARE @sname sysname, @name sysname, @type NVARCHAR(30)
    DECLARE @object_type NVARCHAR(255), @sql NVARCHAR(2000), @count INT = 0

    DECLARE curs CURSOR FOR
        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
    BEGIN
        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''
                    END
        SET @sql = REPLACE(REPLACE(REPLACE(''DROP <TYPE> [<SCHEMA>].[<NAME>];'', 
                        ''<TYPE>'', @object_type),
                        ''<SCHEMA>'', @sname),
                        ''<NAME>'', @name)

        BEGIN TRY  
            PRINT @sql
            EXEC(@sql)
        END TRY  
        BEGIN CATCH  
            PRINT ''ERROR: '' + ERROR_MESSAGE()
        END CATCH  
        FETCH NEXT FROM curs INTO @sname, @name, @type;
    END;

    PRINT CONCAT(''Objects Found: '', @Count)
    PRINT ''''
    PRINT ''------------------------------------------------------''
    PRINT ''''

    CLOSE curs;
    DEALLOCATE curs;

    RETURN @Count
END;
GO

Continuará con los errores (y mostrará el mensaje de error). Devolverá un conteo de todos los objetos encontrados.

Parte 2: Procedimiento de llamada almacenada con parámetros:

Puede crear un bucle WHILE para ejecutar el comando hasta que no quede ningún objeto (dependencias), de la siguiente manera:

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

Parte 3: Finalmente, deshacerse del procedimiento:

IF OBJECT_ID(''_temp_DropAllDnnObjects'') IS NOT NULL
    DROP PROCEDURE _temp_DropAllDnnObjects;
GO

Answer 5

Si te refieres a SQL Server 2005 o superior, usa sys.objects en lugar de 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 introdujo esquemas. Hasta 2000, los usuarios igualaron esquemas. La misma consulta para SQL Server 2000:

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

Answer 6

Solo para repetir lo que ya se ha sugerido aquí, esto es lo que he usado para obtener una lista de Tablas, Procedimientos almacenados, Vistas y Funciones en mi base de datos:

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 
WHERE [type_desc] IN ( ''USER_TABLE'', ''SQL_STORED_PROCEDURE'', ''VIEW'', ''SQL_SCALAR_FUNCTION'')
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

... y esto es lo que nuestro buen amigo Northwind regresaría ...

Answer 7

Yo preferiría usar las vistas "sys" más enfocadas - sys.procedures en lugar de sys.objects. Tendrá que unirlo con la vista sys.schemas para obtener el nombre del esquema y tal.

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

Comenzaría a dejar de usar "sysobjects" ya que Microsoft claramente establece en Libros en Línea que "sysobjects" está sujeto a eliminación en una versión futura:

Esta tabla del sistema de SQL Server 2000 se incluye como una vista para la compatibilidad con versiones anteriores. Recomendamos que use las vistas actuales del sistema SQL Server en su lugar. Para encontrar la vista o vistas equivalentes del sistema, vea Asignar tablas del sistema de SQL Server 2000 a vistas del sistema de SQL Server 2005. Esta característica se eliminará en una versión futura de Microsoft SQL Server. Evite usar esta función en nuevos trabajos de desarrollo y planee modificar las aplicaciones que actualmente usan esta función.

Bagazo