243.字段相关对象查询

USE master

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_HelpTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_HelpTable]
GO

/*--显示列的相关对象

	显示全部表、指定表、指定列上的相关对象信息

*/

/*--调用示例
	
	sp_HelpTable
--*/
CREATE PROC sp_HelpTable
@TableName sysname=NULL,  --要查询的表名,如果为NULL,则查询所有表,可以使用通配符
@FieldName sysname=NULL   --要查询的列名,如果为NULL,则查询所有表,可以使用通配符
AS
--主键/唯一键/索引
SELECT TableName=o.name,FieldName=c.name,ObjectName=idx.name,
	Typeoid=CAST(CASE WHEN oidx.id IS NULL THEN N'INDEX' ELSE N'CONSTRAINT' END as sysname),
	Define=STUFF(CASE
			WHEN oidx.xtype=N'PK' THEN N',PRIMARY KEY'
			WHEN oidx.xtype=N'UQ' THEN N',UNIQUE KEY'
			WHEN INDEXPROPERTY(idxk.id,idx.name,N'IsUnique')=1 THEN N',UNIQUE'
			ELSE N'' END		
		+CASE WHEN INDEXPROPERTY(idxk.id,idx.name,N'IsClustered')=1
			THEN N',CLUSTERED' ELSE N'' END
		+CASE WHEN INDEXPROPERTY(idxk.id,idx.name,N'IsFulltextKey')=1
			THEN N',FulltextKey' ELSE N'' END,1,1,N'')
FROM sysobjects o
	JOIN syscolumns c 
		ON c.id=o.id 
			AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
			AND(o.name LIKE @TableName or @TableName IS NULL)
			AND(c.name LIKE @FieldName or @FieldName IS NULL)
	JOIN sysindexkeys idxk 
		ON idxk.id=o.id 
			AND idxk.colid=c.colid
	JOIN sysindexes idx 
		ON idx.id=idxk.id 
			AND idx.indid=idxk.indid 
			AND idx.indid NOT IN(0,255)
			AND INDEXPROPERTY(idxk.id,idx.name,N'IsAutoStatistics')=0
	LEFT JOIN sysobjects oidx 
		ON oidx.parent_obj=o.id 
			AND oidx.name=idx.name
UNION ALL
--默认值(DEFAULT)
SELECT o.name,c.name,ObjectName=od.name,
	type=CAST(N'DEFAULT' as sysname),
	Define=cm.text
FROM sysobjects o
	JOIN syscolumns c
		ON c.id=o.id 
			AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
			AND(o.name LIKE @TableName or @TableName IS NULL)
			AND(c.name LIKE @FieldName or @FieldName IS NULL)
	JOIN sysobjects od
		ON od.parent_obj=o.id
			AND OBJECTPROPERTY(od.id,N'IsDefaultCnst')=1
	JOIN syscomments cm
		ON cm.id=od.id
			AND cm.id=c.cdefault
UNION ALL
--CHECK约束
SELECT o.name,c.name,ObjectName=oc.name,
	type=CAST(N'CHECK' as sysname),
	Define=cm.text
FROM sysobjects o
	JOIN syscolumns c
		ON c.id=o.id
			AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
			AND(o.name LIKE @TableName or @TableName IS NULL)
			AND(c.name LIKE @FieldName or @FieldName IS NULL)
	JOIN sysobjects oc
		ON  oc.parent_obj=o.id
			AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1
	JOIN syscomments cm
		ON cm.id=oc.id
	JOIN sysdepends d
		ON d.id=oc.id
			AND d.depnumber=c.colid
UNION ALL
--外键约束
SELECT o.name,c.name,ObjectName=ofk.name,
	type=CAST(N'FOREIGNKEY' as sysname),
	Define=QUOTENAME(USER_NAME(opk.uid))
		+N'.'+QUOTENAME(opk.name)
		+N'.'+QUOTENAME(cpk.name)
FROM sysobjects o
	JOIN syscolumns c
		ON c.id=o.id
			AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
			AND(o.name LIKE @TableName or @TableName IS NULL)
			AND(c.name LIKE @FieldName or @FieldName IS NULL)
	JOIN sysobjects ofk
		ON ofk.parent_obj=o.id
			AND OBJECTPROPERTY(ofk.id,N'IsForeignKey')=1
	JOIN sysforeignkeys fk
		ON fk.constid=ofk.id
			AND fk.fkey=c.colid
	JOIN sysobjects opk
		ON opk.id=fk.rkeyid
	JOIN syscolumns cpk
		ON cpk.id=opk.id
			AND cpk.colid=fk.rkey
ORDER BY TableName,FieldName,ObjectName