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

MSDN原文地址

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

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

Transact-SQL
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 函数。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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


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

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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


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

注意
此查询不返回列的名称。
Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

Transact-SQL
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>。

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


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

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




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

Transact-SQL
SELECT definition
FROM sys.server_sql_modules;
GO




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

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

Transact-SQL
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 函数。

Transact-SQL
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>。

Transact-SQL
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>');