243.字段相关对象查询

布满荆棘的人生 2023-12-27 06:49 43阅读 0赞

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