【转自MSDN】SQL Server 系统目录 常用查询语句

原文地址


下列部分按类别列出常见问题。

数据类型

表、索引、视图和约束

模块(存储过程、用户定义函数和触发器)

架构、用户、角色和权限

  • 如何找到指定架构中包含的实体的全部所有者?

  • 如何找到对指定主体授予或拒绝的权限?


    如何找到指定数据库中没有聚集索引的所有表?

    运行下列查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
    FROM sys.tables AS t
    WHERE NOT EXISTS 
       (
         SELECT * FROM sys.indexes AS i
         WHERE i.object_id = t.object_id
         AND i.type = 1  -- or type_desc = 'CLUSTERED'
       )
    ORDER BY schema_name, table_name;
    GO
    
    
    

    或者,可以使用以下示例所显示的 OBJECTPROPERTY 函数。

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
    FROM sys.tables 
    WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
    ORDER BY schema_id, name;
    GO
    
    
    

    页首

    如何找到指定架构中包含的实体的全部所有者?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name>

    USE <database_name>;
    GO
    SELECT 'OBJECT' AS entity_type
        ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
        ,name 
    FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
    UNION 
    SELECT 'TYPE' AS entity_type
        ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
        ,name 
    FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 
    UNION
    SELECT 'XML SCHEMA COLLECTION' AS entity_type 
        ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
        ,xsc.name 
    FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
        ON s.schema_id = xsc.schema_id
    WHERE s.name = '<schema_name>';
    GO
    
    
    

    页首

    如何找到没有主键的所有表?

    运行下列查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(t.schema_id) AS schema_name
        ,t.name AS table_name
    FROM sys.tables t 
    WHERE object_id NOT IN 
       (
        SELECT parent_object_id 
        FROM sys.key_constraints 
        WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
        );
    GO
    
    
    

    或者,可以运行以下查询。

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name
        ,name AS table_name 
    FROM sys.tables 
    WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
    ORDER BY schema_name, table_name;
    GO
    
    
    

    页首

    如何找到没有索引的所有表?

    运行以下查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name
        ,name AS table_name
    FROM sys.tables 
    WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
    ORDER BY schema_name, table_name;
    GO
    
    
    

    页首

    如何找到具有标识列的所有表?

    运行以下查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name
        , t.name AS table_name
        , c.name AS column_name
    FROM sys.tables AS t
    JOIN sys.identity_columns c ON t.object_id = c.object_id
    ORDER BY schema_name, table_name;
    GO
    
    
    

    或者,可以运行以下查询。

    注意 注意

    此查询不返回列的名称。

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name
        ,name AS table_name 
    FROM sys.tables 
    WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
    ORDER BY schema_name, table_name;
    GO
    
    
    

    页首

    如何找到指定表中列的数据类型?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT c.name AS column_name
        ,c.column_id
        ,SCHEMA_NAME(t.schema_id) AS type_schema
        ,t.name AS type_name
        ,t.is_user_defined
        ,t.is_assembly_type
        ,c.max_length
        ,c.precision
        ,c.scale
    FROM sys.columns AS c 
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
    ORDER BY c.column_id;
    GO
    
    
    

    页首

    如何找到指定函数的依赖项?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.function_name>

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS referencing_object_name
        ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
        ,*
    FROM sys.sql_dependencies
    WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')
    ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
    GO 
    
    
    

    页首

    如何找到数据库中的所有存储过程?

    运行以下查询之前,请使用有效名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT name AS procedure_name 
        ,SCHEMA_NAME(schema_id) AS schema_name
        ,type_desc
        ,create_date
        ,modify_date
    FROM sys.procedures;
    GO
    
    
    

    页首

    如何找到指定存储过程或函数的参数?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.object_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(schema_id) AS schema_name
        ,o.name AS object_name
        ,o.type_desc
        ,p.parameter_id
        ,p.name AS parameter_name
        ,TYPE_NAME(p.user_type_id) AS parameter_type
        ,p.max_length
        ,p.precision
        ,p.scale
        ,p.is_output
    FROM sys.objects AS o
    INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
    WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
    ORDER BY schema_name, o.object_name, p.parameter_id;
    GO
    
    
    

    页首

    如何找到数据库中的所有用户定义函数?

    运行以下查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT name AS function_name 
      ,SCHEMA_NAME(schema_id) AS schema_name
      ,type_desc
      ,create_date
      ,modify_date
    FROM sys.objects
    WHERE type_desc LIKE '%FUNCTION%';
    GO
    
    
    

    页首

    如何找到数据库中的所有视图?

    运行以下查询之前,请使用有效数据库名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT name AS view_name 
      ,SCHEMA_NAME(schema_id) AS schema_name
      ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
      ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
      ,create_date
      ,modify_date
    FROM sys.views;
    GO
    
    
    

    页首

    如何找到最近 n 天内修改过的所有实体?

    运行以下查询之前,请使用有效值替换 <database_name> 和 <n_days>

    USE <database_name>;
    GO
    SELECT name AS object_name 
      ,SCHEMA_NAME(schema_id) AS schema_name
      ,type_desc
      ,create_date
      ,modify_date
    FROM sys.objects
    WHERE modify_date > GETDATE() - <n_days>
    ORDER BY modify_date;
    GO
    
    
    

    页首

    如何找到指定表中的 LOB 数据类型?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT name AS column_name 
        ,column_id 
        ,TYPE_NAME(user_type_id) AS type_name
        ,max_length
        ,CASE 
           WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'
                THEN 1
                ELSE 0
         END AS [(max)]
    FROM sys.columns
    WHERE object_id=OBJECT_ID('<schema_name.table_name>') 
        AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')
             OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')
             AND max_length = -1)
            );
    GO
    
    
    

    页首

    如何查看模块定义?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.object_name>

    USE <database_name>;
    GO
    SELECT definition
    FROM sys.sql_modules
    WHERE object_id = OBJECT_ID('<schema_name.object_name>');
    GO
    
    
    

    或者,可以使用以下示例所显示的 OBJECT_DEFINITION 函数。

    USE <database_name>;
    GO
    SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
    GO
    
    
    

    页首

    如何查看服务器级别触发器的定义?

    SELECT definition
    FROM sys.server_sql_modules;
    GO
    
    
    

    页首

    如何找到指定表的主键列?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT i.name AS index_name
        ,ic.index_column_id
        ,key_ordinal
        ,c.name AS column_name
        ,TYPE_NAME(c.user_type_id)AS column_type 
        ,is_identity
    FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS ic 
        ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN sys.columns AS c 
        ON ic.object_id = c.object_id AND c.column_id = ic.column_id
    WHERE i.is_primary_key = 1 
        AND i.object_id = OBJECT_ID('<schema_name.table_name>');
    GO
    
    
    

    或者,可以使用以下示例所显示的 COL_NAME 函数。

    USE <database_name>;
    GO
    SELECT i.name AS index_name
        ,COL_NAME(ic.object_id,ic.column_id) AS column_name
        ,ic.index_column_id
        ,key_ordinal
    FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS ic 
        ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE i.is_primary_key = 1 
        AND i.object_id = OBJECT_ID('<schema_name.table_name>');
    GO
    
    
    

    页首

    如何找到指定表的外键列?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT 
        f.name AS foreign_key_name
       ,OBJECT_NAME(f.parent_object_id) AS table_name
       ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
       ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
       ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
       ,is_disabled
       ,delete_referential_action_desc
       ,update_referential_action_desc
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc 
       ON f.object_id = fc.constraint_object_id 
    WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');
    
    
    

    页首

    如何找到对指定主体授予或拒绝的权限?

    以下示例创建函数以返回检查对其权限的实体的名称。 在下列查询中调用函数。 必须在每个数据库(要在其中检查权限)中创建函数。

    -- Create a function to return the name of the entity on which the permissions are checked.
    IF OBJECT_ID (N'dbo.entity_instance_name', N'FN') IS NOT NULL
        DROP FUNCTION dbo.entity_instance_name;
    GO
    CREATE FUNCTION dbo.entity_instance_name(@class_desc nvarchar(60), @major_id int) 
    RETURNS sysname AS
    BEGIN
        DECLARE @the_entity_name sysname
        SELECT @the_entity_name = CASE
            WHEN @class_desc = 'DATABASE' THEN DB_NAME()
            WHEN @class_desc = 'SCHEMA' THEN SCHEMA_NAME(@major_id)
            WHEN @class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(@major_id)
            WHEN @class_desc = 'DATABASE_PRINCIPAL' THEN USER_NAME(@major_id)
            WHEN @class_desc = 'ASSEMBLY' THEN 
                (SELECT name FROM sys.assemblies WHERE assembly_id=@major_id)
            WHEN @class_desc = 'TYPE' THEN TYPE_NAME(@major_id)
            WHEN @class_desc = 'XML_SCHEMA_COLLECTION' THEN 
                (SELECT name FROM sys.xml_schema_collections
                  WHERE xml_collection_id=@major_id)
            WHEN @class_desc = 'MESSAGE_TYPE' THEN 
                (SELECT name FROM sys.service_message_types WHERE message_type_id=@major_id)
            WHEN @class_desc = 'SERVICE_CONTRACT' THEN 
               (SELECT name FROM sys.service_contracts
                  WHERE service_contract_id=@major_id)
            WHEN @class_desc = 'SERVICE' THEN
              (SELECT name FROM sys.services WHERE service_id=@major_id)
            WHEN @class_desc = 'REMOTE_SERVICE_BINDING' THEN
              (SELECT name FROM sys.remote_service_bindings
                 WHERE remote_service_binding_id=@major_id)
            WHEN @class_desc = 'ROUTE' THEN
              (SELECT name FROM sys.routes WHERE route_id=@major_id)
            WHEN @class_desc = 'FULLTEXT_CATALOG' THEN
              (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=@major_id)
            WHEN @class_desc = 'SYMMETRIC_KEY' THEN
              (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id=@major_id)
            WHEN @class_desc = 'CERTIFICATE' THEN
              (SELECT name FROM sys.certificates WHERE certificate_id=@major_id)
            WHEN @class_desc = 'ASYMMETRIC_KEY' THEN
              (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id=@major_id)
            WHEN @class_desc = 'SERVER' THEN 
                 (SELECT name FROM sys.servers WHERE server_id=@major_id)
            WHEN @class_desc = 'SERVER_PRINCIPAL' THEN SUSER_NAME(@major_id)
            WHEN @class_desc = 'ENDPOINT' THEN 
                 (SELECT name FROM sys.endpoints WHERE endpoint_id=@major_id)      
            ELSE '?'
        END
        RETURN @the_entity_name
    END;
    GO
    -- Return server-level permissions for the user.
    SELECT class
        ,class_desc
        ,dbo.entity_instance_name(class_desc, major_id) AS entity_name 
        ,minor_id
        ,SUSER_NAME(grantee_principal_id) AS grantee
        ,SUSER_NAME(grantor_principal_id) AS grantor
        ,type
        ,permission_name
        ,state_desc 
    FROM sys.server_permissions 
    WHERE grantee_principal_id = SUSER_ID('public');
    GO
    -- Return database-level permissions for the user.
    SELECT class
        ,class_desc
        ,dbo.entity_instance_name(class_desc , major_id) AS entity_name 
        ,minor_id
        ,USER_NAME(grantee_principal_id) AS grantee
        ,USER_NAME(grantor_principal_id) AS grantor
        ,type
        ,permission_name
        ,state_desc   
    FROM  sys.database_permissions 
    WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public');
    GO
    
    
    

    页首

    如何确定某列是否在计算列表达式中使用?

    运行以下查询之前,请使用有效名称替换 <database_name><schema_name.table_name> 和 <column_name>。

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name
        ,COL_NAME(object_id, column_id) AS computed_column 
        ,class_desc
        ,is_selected
        ,is_updated
        ,is_select_all
    FROM sys.sql_dependencies
    WHERE referenced_major_id = OBJECT_ID('<schema_name.table_name>')
        AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '<column_name>', 'ColumnId')
        AND class = 1;
    GO
    
    
    

    页首

    如何找到在计算列表达式中使用的所有列?

    运行以下查询之前,请使用有效名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
        ,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
        ,OBJECT_NAME(referenced_major_id) AS dependent_object_name 
        ,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
        ,cc.definition AS computed_column_definition
    FROM sys.sql_dependencies AS d
    JOIN sys.computed_columns AS cc 
        ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id     
    WHERE d.class = 1
    ORDER BY object_name, column_name;
    GO
    
    
    

    页首

    如何找到依赖于指定 CLR 用户定义类型或别名类型的列?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的架构限定的 CLR 用户定义类型或架构限定的别名类型名称替换 <schema_name.data_type_name> 以下查询需要db_owner 角色的成员身份或者查看数据库中所有依赖列和计算列元数据的权限。

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name 
        ,c.name AS column_name 
        ,SCHEMA_NAME(t.schema_id) AS schema_name
        ,TYPE_NAME(c.user_type_id) AS user_type_name
        ,c.max_length
        ,c.precision
        ,c.scale
        ,c.is_nullable
        ,c.is_computed
    FROM sys.columns AS c
    INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
    WHERE c.user_type_id = TYPE_ID('<schema_name.data_type_name>'); 
    GO
    
    
    

    以下查询返回依赖于 CLR 用户定义类型或别名的列的受限窄视图,但其结果集对 public 角色可见。 如果您已经将您的用户定义类型的 REFERENCE 权限授予了其他人,并且您没有权限查看其他人使用该类型创建的对象的元数据,则可以使用此查询。

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name 
        ,COL_NAME(object_id, column_id) AS column_name
        ,TYPE_NAME(user_type_id) AS user_type
    FROM sys.column_type_usages
    WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');
    GO
    
    
    

    页首

    如何找到依赖于指定 CLR 用户定义类型或别名类型的计算列?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的架构限定的 CLR 用户定义类型和别名类型名称替换 <schema_name.data_type_name>

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name
        ,COL_NAME(object_id, column_id) AS column_name
    FROM sys.sql_dependencies
    WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
        AND class = 2 -- schema-bound references to type
        AND OBJECTPROPERTY(object_id, 'IsTable') = 1;   -- exclude non-table dependencies 
    
    
    

    TOP

    如何找到依赖于指定 CLR 用户定义类型或别名类型的参数?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的架构限定的 CLR 用户定义类型和别名类型名称替换 <schema_name.data_type_name> 以下查询需要 db_owner 角色的成员身份或者查看数据库中所有依赖列和计算列元数据的权限。

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name
        ,NULL AS procedure_number
        ,name AS param_name
        ,parameter_id AS param_num
        ,TYPE_NAME(p.user_TYPE_ID) AS type_name
    FROM sys.parameters AS p
    WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')
    UNION 
    SELECT OBJECT_NAME(object_id) AS object_name
        ,procedure_number
        ,name AS param_name
        ,parameter_id AS param_num
        ,TYPE_NAME(p.user_TYPE_ID) AS type_name
    FROM sys.numbered_procedure_parameters AS p
    WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')
    ORDER BY object_name, procedure_number, param_num;
    GO
    
    
    
    

    以下查询返回依赖于 CLR 用户定义类型或别名的参数的受限窄视图,但其结果集对 public 角色可见。 如果您已经将您的用户定义类型的 REFERENCE 权限授予了其他人,并且您没有权限查看其他人使用该类型创建的对象的元数据,则可以使用此查询。

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) AS object_name
        ,parameter_id
        ,TYPE_NAME(user_type_id) AS type_name
    FROM sys.parameter_type_usages 
    WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');
    GO
    
    
    

    页首

    如何找到依赖于指定 CLR 用户定义类型的 CHECK 约束?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的架构限定的 CLR 用户定义类型名称替换 <schema_name.data_type_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(o.schema_id) AS schema_name
        ,OBJECT_NAME(o.parent_object_id) AS table_name
        ,OBJECT_NAME(o.object_id) AS constraint_name
    FROM sys.sql_dependencies AS d
    JOIN sys.objects AS o ON o.object_id = d.object_id
    WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
        AND class = 2 -- schema-bound references to type
        AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies
    GO
    
    
    

    页首

    如何找到依赖于指定 CLR 用户定义类型或别名类型的视图、Transact-SQL 函数和 Transact-SQL 存储过程?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的架构限定的 CLR 用户定义类型和别名类型名称替换 <schema_name.data_type_name>

    在函数或过程中定义的参数为隐式架构绑定。 因此,可以使用 sys.sql_dependencies 目录视图查看依赖于 CLR 用户定义类型或别名类型的参数。 过程和触发器均未绑定到架构。 这意味着不会维护任何在过程或触发器的主体中定义的表达式与 CLR 用户定义类型或别名类型之间的依赖关系。 架构绑定视图和架构绑定用户定义函数具有依赖于 CLR 用户定义类型或别名类型的表达式,在 sys.sql_dependencies 目录视图中维护。 不维护类型和 CLR 函数及类型和 CLR 过程之间的依赖关系。

    以下查询返回指定 CLR 用户定义类型或别名类型在视图、Transact-SQL 函数和 Transact-SQL 存储过程中的所有架构绑定依赖关系。

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema
      ,OBJECT_NAME(o.object_id) AS dependent_object_name
      ,o.type_desc AS dependent_object_type
      ,d.class_desc AS kind_of_dependency
      ,TYPE_NAME (d.referenced_major_id) AS type_name
    FROM sys.sql_dependencies AS d 
    JOIN sys.objects AS o
      ON d.object_id = o.object_id
      AND o.type IN ('FN','IF','TF', 'V', 'P')
    WHERE d.class = 2 -- dependencies on types
      AND d.referenced_major_id = TYPE_ID('<schema_name.data_type_name>')
    ORDER BY dependent_object_schema, dependent_object_name;
    GO
    
    
    

    页首

    如何找到指定表的所有约束?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id) as constraint_name
        ,SCHEMA_NAME(schema_id) AS schema_name
        ,OBJECT_NAME(parent_object_id) AS table_name
        ,type_desc
        ,create_date
        ,modify_date
        ,is_ms_shipped
        ,is_published
        ,is_schema_published
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT' 
        AND parent_object_id = OBJECT_ID('<schema_name.table_name>');
    GO
    
    
    

    页首

    如何找到指定表的所有索引?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.table_name>

    USE <database_name>;
    GO
    SELECT i.name AS index_name
        ,i.type_desc
        ,is_unique
        ,ds.type_desc AS filegroup_or_partition_scheme
        ,ds.name AS filegroup_or_partition_scheme_name
        ,ignore_dup_key
        ,is_primary_key
        ,is_unique_constraint
        ,fill_factor
        ,is_padded
        ,is_disabled
        ,allow_row_locks
        ,allow_page_locks
    FROM sys.indexes AS i
    INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
    WHERE is_hypothetical = 0 AND i.index_id <> 0 
    AND i.object_id = OBJECT_ID('<schema_name.table_name>');
    GO
    
    
    

    页首

    如何找到具有指定列名称的所有对象?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <column_name>

    USE <database_name>;
    GO
    SELECT OBJECT_NAME(object_id)
    FROM sys.columns
    WHERE name = '<column_name>';
    GO
    
    
    

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(o.schema_id) AS schema_name 
        ,o.name AS object_name
        ,type_desc
    FROM sys.objects AS o
    INNER JOIN sys.columns AS c ON o.object_id = c.object_id
    WHERE c.name = '<column_name>';
    GO
    
    
    

    页首

    如何找到指定数据库中的所有用户定义表?

    运行以下查询之前,请使用有效名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT * 
    FROM sys.tables;
    GO
    
    
    

    页首

    如何找到所有进行了分区的表和索引?

    运行以下查询之前,请使用有效名称替换 <database_name>

    USE <database_name>;
    GO
    SELECT SCHEMA_NAME(o.schema_id) AS schema_name
        ,OBJECT_NAME(p.object_id) AS table_name
        ,i.name AS index_name
        ,p.partition_number
        ,rows 
    FROM sys.partitions AS p
    INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
    INNER JOIN sys.objects AS o ON o.object_id = i.object_id
    ORDER BY index_name, partition_number;
    GO
    
    
    

    页首

    如何找到指定对象的所有统计信息?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的表、索引视图或表值函数名称替换 <schema_name.object_name>

    USE <database_name>;
    GO
    SELECT name AS statistics_name
        ,stats_id
        ,auto_created
        ,user_created
        ,no_recompute
    FROM sys.stats
    WHERE object_id = OBJECT_ID('<schema_name.object_name>');
    GO
    
    
    

    页首

    如何找到指定对象的所有统计信息和统计信息列?

    运行以下查询之前,请使用有效名称替换 <database_name>,并使用有效的表、索引视图或表值函数名称替换 <schema_name.object_name>

    USE <database_name>;
    GO
    SELECT s.name AS statistics_name
        ,c.name AS column_name
        ,sc.stats_column_id
    FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc 
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
    INNER JOIN sys.columns AS c 
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id
    WHERE s.object_id = OBJECT_ID('<schema_name.object_name>');
    GO
    
    
    

    页首

    如何找到视图的定义?

    运行以下查询之前,请使用有效名称替换 <database_name> 和 <schema_name.object_name>

    USE <database_name>;
    GO
    SELECT definition
    FROM sys.sql_modules
    WHERE object_id = OBJECT_ID('<schema_name.object_name>');
    GO
    
    
    

    或者,可以使用以下示例所显示的 OBJECT_DEFINITION 函数。

    USE <database_name>;
    GO
    SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
    GO
    
    


相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页