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

guguda2008 2012-07-10 11:18:48
加精
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>');


...全文
1464 点赞 收藏 60
写回复
60 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dpshui 2012-08-08
感谢分享
回复
holicc 2012-08-08
CLR 用户定义类型是什么?能否简单说明,
回复
holicc 2012-08-08
谢谢分享,顺便说一下:
“如何找到指定存储过程或函数的参数?”代码里面有错误,不过是小问题了。在这里贴一下:
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.name, p.parameter_id;

继续往下看……
回复
wanglu_170858188 2012-07-24
[Quote=引用 54 楼 的回复:]
感谢分享。收藏下
[/Quote]

http://topic.csdn.net/u/20120724/15/9a1eb0fe-e8ab-45c8-91df-faf84816b027.html

大哥 这个100分!等你来解答呢! 嫌少的话 我目前还有50分!等结贴后 的50也给你!一共200分!
回复
Barton 2012-07-24
感谢分享。收藏下
回复
Delta 2012-07-23
[Quote=引用 28 楼 的回复:]

太强大了!支持一下
[/Quote]学习了
回复
yfbill1314 2012-07-21
挺好的资料,很有帮助!
回复
lgm24688 2012-07-20
感谢楼主分享!!!!
回复
zhazhuzhao 2012-07-18
不错不错,灌灌水!
回复
很实用
回复
上岸の鱼 2012-07-18
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name....

这个SCHEMA_NAME不能识别,这是为什么,是因为我用的是sql server 2000么?
回复
关键是需要用到系统表
回复
nettman 2012-07-15
感谢楼主分享!
回复
wjw220 2012-07-15
多谢分享xiexie
回复
有点用,谢谢分享,
收藏下
回复
Rotel-刘志东 2012-07-14
支持一下了。
回复
yueqianbuer66 2012-07-12
好东西 值得看看
回复
lao_bulls 2012-07-11
谢谢鸭子分享
回复
majun831112 2012-07-11
有点用,可是我初学就是很多没看懂
回复
line_us 2012-07-11
先顶起。
慢慢看
回复
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2012-07-10 11:18
社区公告
暂无公告