如下該sp_TableInfo的用途？

CREATE PROCEDURE sp_TableInfo

(@TABLES VARCHAR(8000)='',@WITHINDEX Bit=0)  
AS  
DECLARE @TABLENAMES TABLE(TABLENAME VARCHAR(80)) 
IF RTRIM(@TABLES)=''  
BEGIN 
 IF EXISTS(SELECT * FROM master.dbo.sysobjects where name = 'sysproperties')  
  SELECT CAST( CASE WHEN C.COLID>1 THEN'' ELSE O.NAME END AS VARCHAR(20))+ 
                 CAST( ISNULL((SELECT '('+CAST(VALUE AS VARCHAR(50))+')' FROM SYSPROPERTIES WHERE ID=O.ID AND TYPE=3 AND C.COLID=1),'') AS VARCHAR(50))  '表名'  
   , C.COLID '字段序號'  
   , ISNULL( (SELECT '＊' FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE  (I.STATUS & 0X800)=0X800 AND I.ID=IK.ID  AND I.INDID=IK.INDID AND I.ID=C.ID AND IK.COLID=C.COLID),'') + C.NAME  '字段名'                
   , ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=C.ID AND SMALLID=C.COLID AND TYPE=4),'')  AS  '字段說明'   
   , CASE WHEN  T.NAME  IN('BINARY','CHAR','NCHAR','NVARCHAR','VARBINARY','VARCHAR') THEN  T.NAME + '(' + CAST(C.LENGTH AS VARCHAR(4))  + ')'   
                WHEN T.NAME  IN('DECIMAL','NUMERIC') THEN T.NAME + '(' + CAST(C.XPREC AS VARCHAR(2)) + ',' + CAST(C.XSCALE AS VARCHAR(2)) + ')'  
                ELSE T.NAME END '數據類型' 
   , CASE WHEN C.ISNULLABLE=1 THEN '√' ELSE '' END '允許NULL' 
   , ISNULL((SELECT TEXT FROM SYSCOMMENTS WHERE ID=C.CDEFAULT),'') '預設值' 
   , ISNULL((SELECT R.NAME FROM SYSFOREIGNKEYS FK , SYSOBJECTS R WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID),'') '引用表'  
   , ISNULL((SELECT RC.NAME FROM SYSFOREIGNKEYS FK ,SYSOBJECTS R ,  SYSCOLUMNS RC WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID AND RC.ID=R.ID AND RC.COLID=FK.RKEY ),'')  '引用字段'   
   , ISNULL((SELECT TOP 1 '√'  FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE I.ID=IK.ID AND I.INDID=IK.INDID AND IK.ID=C.ID AND IK.COLID=C.COLID),'') '是否索引'   
   , CASE WHEN C.COLID>1 THEN'' ELSE 'Exec sp_TableDesc '''+O.Name+''','''+Cast(ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=O.ID AND SMALLID=0 AND TYPE=3),'') as varchar(800) )+'''' END  AS '新增(修改)表說明腳本' 
   , 'Exec sp_TableFieldDesc '''+O.Name+''' ,'''+C.Name +''','''+Cast(ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=C.ID AND SMALLID=C.COLID AND TYPE=4),'') as varchar(800) )+''''  AS '新增(修改)字段說明腳本' 
  FROM SYSOBJECTS O  
  JOIN SYSCOLUMNS C ON C.ID = O.ID 
  JOIN SYSTYPES T  ON  T.XUSERTYPE =  C.XTYPE 
  WHERE O.XTYPE='U'  
  ORDER BY  O.NAME  , C.COLID -- '表名', '字段名'  
 ELSE  
  SELECT CAST( CASE WHEN C.COLID>1 THEN'' ELSE O.NAME END AS VARCHAR(20))+ 
                 CAST( ISNULL((SELECT '('+CAST(VALUE AS VARCHAR(50))+')' FROM sys.extended_properties WHERE major_id=O.ID AND minor_id=0 AND C.COLID=1),'') AS VARCHAR(50))  '表名'  
   , C.COLID '字段序號'  
   , ISNULL( (SELECT '＊' FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE  (I.STATUS & 0X800)=0X800 AND I.ID=IK.ID  AND I.INDID=IK.INDID AND I.ID=C.ID AND IK.COLID=C.COLID),'') + C.NAME  '字段名'                
   , ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=C.ID AND minor_id=C.COLID),'')  AS  '字段說明'   
   , CASE WHEN  T.NAME  IN('BINARY','CHAR','NCHAR','NVARCHAR','VARBINARY','VARCHAR') THEN  T.NAME + '(' + CAST(C.LENGTH AS VARCHAR(4))  + ')'   
                WHEN T.NAME  IN('DECIMAL','NUMERIC') THEN T.NAME + '(' + CAST(C.XPREC AS VARCHAR(2)) + ',' + CAST(C.XSCALE AS VARCHAR(2)) + ')'  
                ELSE T.NAME END '數據類型' 
   , CASE WHEN C.ISNULLABLE=1 THEN '√' ELSE '' END '允許NULL' 
   , ISNULL((SELECT TEXT FROM SYSCOMMENTS WHERE ID=C.CDEFAULT),'') '預設值' 
   , ISNULL((SELECT R.NAME FROM SYSFOREIGNKEYS FK , SYSOBJECTS R WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID),'') '引用表'  
   , ISNULL((SELECT RC.NAME FROM SYSFOREIGNKEYS FK ,SYSOBJECTS R ,  SYSCOLUMNS RC WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID AND RC.ID=R.ID AND RC.COLID=FK.RKEY ),'')  '引用字段'   
   , ISNULL((SELECT TOP 1 '√'  FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE I.ID=IK.ID AND I.INDID=IK.INDID AND IK.ID=C.ID AND IK.COLID=C.COLID),'') '是否索引'   
   , CASE WHEN C.COLID>1 THEN'' ELSE 'Exec sp_TableDesc '''+O.Name+''','''+Cast(ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=O.ID AND minor_id=0 ),'') as varchar(800) )+'''' END  AS '新增(修改)表說明腳本' 
   , 'Exec sp_TableFieldDesc '''+O.Name+''' ,'''+C.Name +''','''+Cast(ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=C.ID AND major_id=C.COLID),'') as varchar(800) )+''''  AS '新增(修改)字段說明腳本' 
  FROM SYSOBJECTS O  
  JOIN SYSCOLUMNS C ON C.ID = O.ID 
  JOIN SYSTYPES T  ON  T.XUSERTYPE =  C.XTYPE 
  WHERE O.XTYPE='U'   
  ORDER BY  O.NAME  , C.COLID -- '表名', '字段名'  
 
 --索引 
 IF @WITHINDEX=1 
 BEGIN 
  
  SELECT  IDENTITY(INT, 1,1) SEQ 
   , O.NAME '表名' 
   , C.NAME '字段名' 
   , CASE WHEN IK.KEYNO=1 THEN I.NAME ELSE '' END  '索引名' 
   , CASE WHEN IK.KEYNO=1 AND IK.INDID=1 THEN '叢集索引' WHEN IK.KEYNO=1 AND IK.INDID>1 THEN  '非叢集索引' ELSE '' END'索引類別' 
  INTO #T 
  FROM SYSOBJECTS O  
  JOIN SYSCOLUMNS C  ON C.ID=O.ID  
  JOIN SYSINDEXKEYS IK ON IK.COLID = C.COLID  
  JOIN SYSINDEXES I  ON  I.ID = IK.ID 
  ORDER BY O.NAME , IK.INDID , C.COLID 
 
  SELECT CASE WHEN T.SEQ=(SELECT MIN(SEQ) FROM #T WHERE [表名]=T.[表名] ) THEN T.[表名] ELSE '' END [表名] 
   ,T.[字段名] 
   ,T.[索引名] 
   ,T.[索引類別] 
  FROM #T T ORDER BY SEQ 
 
  DROP TABLE #T 
 END 
END  
ELSE 
BEGIN 
 DECLARE @TABLENAME VARCHAR(100) 
 SET  @TABLENAME='' 
 WHILE  CHARINDEX(',',@TABLES,0)>0 
 BEGIN  
  INSERT INTO @TABLENAMES SELECT LTRIM(RTRIM(LEFT(@TABLES,CHARINDEX(',',@TABLES,0)-1))) 
  SET @TABLES=RIGHT(@TABLES,LEN(@TABLES)-CHARINDEX(',',@TABLES,0))  
 END  
 INSERT INTO @TABLENAMES SELECT  LTRIM(RTRIM(@TABLES)) 
 
 IF EXISTS(SELECT * FROM master.dbo.sysobjects where name = 'sysproperties')  
  SELECT  CAST( CASE WHEN C.COLID>1 THEN'' ELSE O.NAME END AS VARCHAR(20))+ 
                                 CAST( ISNULL((SELECT '('+CAST(VALUE AS VARCHAR(50))+')' FROM SYSPROPERTIES WHERE ID=O.ID AND TYPE=3 AND C.COLID=1),'') AS VARCHAR(50))  '表名'  
   , C.COLID '字段序號'           
   , ISNULL( (SELECT '＊' FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE  (I.STATUS & 0X800)=0X800 AND I.ID=IK.ID  AND I.INDID=IK.INDID AND I.ID=C.ID AND IK.COLID=C.COLID),'') +  C.NAME  '字段名'      
   , ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=C.ID AND SMALLID=C.COLID AND TYPE=4),'')  AS  '字段說明'   
   , CASE WHEN  T.NAME  IN('BINARY','CHAR','NCHAR','NVARCHAR','VARBINARY','VARCHAR') THEN  T.NAME + '(' + CAST(C.LENGTH AS VARCHAR(4))  + ')'   
                WHEN T.NAME  IN('DECIMAL','NUMERIC') THEN T.NAME + '(' + CAST(C.XPREC AS VARCHAR(2)) + ',' + CAST(C.XSCALE AS VARCHAR(2)) + ')'  
                ELSE T.NAME END '數據類型' 
   , CASE WHEN C.ISNULLABLE=1 THEN '√' ELSE '' END '允許NULL' 
   , ISNULL((SELECT TEXT FROM SYSCOMMENTS WHERE ID=C.CDEFAULT),'') '預設值' 
   , ISNULL((SELECT R.NAME FROM SYSFOREIGNKEYS FK , SYSOBJECTS R WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID),'') '引用表'  
   , ISNULL((SELECT RC.NAME FROM SYSFOREIGNKEYS FK ,SYSOBJECTS R ,  SYSCOLUMNS RC WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID AND RC.ID=R.ID AND RC.COLID=FK.RKEY ),'')  '引用字段'   
   , ISNULL((SELECT TOP 1 '√'  FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE I.ID=IK.ID AND I.INDID=IK.INDID AND IK.ID=C.ID AND IK.COLID=C.COLID),'') '是否索引'      
   , CASE WHEN C.COLID>1 THEN'' ELSE 'Exec sp_TableDesc '''+O.Name+''','''+Cast(ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=O.ID AND SMALLID=0 AND TYPE=3),'') as varchar(800) )+'''' END  AS '新增(修改)表說明腳本' 
   , 'Exec sp_TableFieldDesc '''+O.Name+''' ,'''+C.Name +''','''+Cast(ISNULL((SELECT VALUE FROM SYSPROPERTIES WHERE ID=C.ID AND SMALLID=C.COLID AND TYPE=4),'') as varchar(800) )+'''' AS '新增(修改)字段說明腳本' 
  FROM SYSOBJECTS O  
  JOIN  SYSCOLUMNS C ON C.ID = O.ID   
  JOIN SYSTYPES T ON T.XUSERTYPE = C.XTYPE  
  WHERE O.XTYPE='U' AND O.NAME  IN(SELECT TABLENAME FROM @TABLENAMES) 
  ORDER BY O.NAME  , C.COLID 
 ELSE  
  SELECT  CAST( CASE WHEN C.COLID>1 THEN'' ELSE O.NAME END AS VARCHAR(20))+ 
                                 CAST( ISNULL((SELECT '('+CAST(VALUE AS VARCHAR(50))+')' FROM sys.extended_properties WHERE major_id=O.ID AND minor_id=0 AND C.COLID=1),'') AS VARCHAR(50))  '表名'  
   , C.COLID '字段序號'           
   , ISNULL( (SELECT '＊' FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE  (I.STATUS & 0X800)=0X800 AND I.ID=IK.ID  AND I.INDID=IK.INDID AND I.ID=C.ID AND IK.COLID=C.COLID),'') +  C.NAME  '字段名'      
   , ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=C.ID AND minor_id=C.COLID),'')  AS  '字段說明'   
   , CASE WHEN  T.NAME  IN('BINARY','CHAR','NCHAR','NVARCHAR','VARBINARY','VARCHAR') THEN  T.NAME + '(' + CAST(C.LENGTH AS VARCHAR(4))  + ')'   
                WHEN T.NAME  IN('DECIMAL','NUMERIC') THEN T.NAME + '(' + CAST(C.XPREC AS VARCHAR(2)) + ',' + CAST(C.XSCALE AS VARCHAR(2)) + ')'  
                ELSE T.NAME END '數據類型' 
   , CASE WHEN C.ISNULLABLE=1 THEN '√' ELSE '' END '允許NULL' 
   , ISNULL((SELECT TEXT FROM SYSCOMMENTS WHERE ID=C.CDEFAULT),'') '預設值' 
   , ISNULL((SELECT R.NAME FROM SYSFOREIGNKEYS FK , SYSOBJECTS R WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID),'') '引用表'  
   , ISNULL((SELECT RC.NAME FROM SYSFOREIGNKEYS FK ,SYSOBJECTS R ,  SYSCOLUMNS RC WHERE R.ID=FK.RKEYID AND FK.FKEYID=C.ID AND FK.FKEY=C.COLID AND RC.ID=R.ID AND RC.COLID=FK.RKEY ),'')  '引用字段'   
   , ISNULL((SELECT TOP 1 '√'  FROM SYSINDEXKEYS IK , SYSINDEXES I WHERE I.ID=IK.ID AND I.INDID=IK.INDID AND IK.ID=C.ID AND IK.COLID=C.COLID),'') '是否索引'      
   , CASE WHEN C.COLID>1 THEN'' ELSE 'Exec sp_TableDesc '''+O.Name+''','''+Cast(ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=O.ID AND minor_id=0),'') as varchar(800) )+'''' END  AS '新增(修改)表說明腳本' 
   , 'Exec sp_TableFieldDesc '''+O.Name+''' ,'''+C.Name +''','''+Cast(ISNULL((SELECT VALUE FROM sys.extended_properties WHERE major_id=C.ID AND minor_id=C.COLID),'') as varchar(800) )+'''' AS '新增(修改)字段說明腳本' 
  FROM SYSOBJECTS O  
  JOIN  SYSCOLUMNS C ON C.ID = O.ID   
  JOIN SYSTYPES T ON T.XUSERTYPE = C.XTYPE  
  WHERE O.XTYPE='U'  AND O.NAME  IN(SELECT TABLENAME FROM @TABLENAMES) 
  ORDER BY O.NAME  , C.COLID 
 
 --索引 
 IF @WITHINDEX=1 
 BEGIN  
  --索引 
  SELECT  IDENTITY(INT, 1,1) SEQ 
   , O.NAME '表名' 
   , C.NAME '字段名' 
   , CASE WHEN IK.KEYNO=1 THEN I.NAME ELSE '' END  '索引名' 
   , CASE WHEN IK.KEYNO=1 AND IK.INDID=1 THEN '叢集索引' WHEN IK.KEYNO=1 AND IK.INDID>1 THEN  '非叢集索引' ELSE '' END'索引類別' 
  INTO #TT 
  FROM SYSOBJECTS O  
  JOIN SYSCOLUMNS C  ON C.ID = O.ID  
  JOIN SYSINDEXKEYS IK  ON IK.COLID = C.COLID  
  JOIN SYSINDEXES I ON I.ID=IK.ID AND I.INDID=IK.INDID AND I.ID=C.ID AND IK.COLID=C.COLID  
  WHERE O.NAME  IN(SELECT TABLENAME FROM @TABLENAMES) 
  ORDER BY O.NAME , IK.INDID , C.COLID 
 
  SELECT CASE WHEN T.SEQ=(SELECT MIN(SEQ) FROM #TT WHERE [表名]=T.[表名] ) THEN T.[表名] ELSE '' END [表名] 
   ,T.[字段名] 
   ,T.[索引名] 
   ,T.[索引類別] 
  FROM #TT T  ORDER BY SEQ 
 
  DROP TABLE #TT 
 END 
END   
 

转载于:https://www.cnblogs.com/Jefft/archive/2010/07/07/PROCEDURE.html