【通用】获取表结构、索引、主外键的存储过程

非常实用的SQL,希望大家喜欢。

 

---------------Table2012/7/30 19:29:05---------------------
use master
go

 CREATE PROCEDURE sp_helptableinfo
 (
   @tableName VARCHAR(50),
   @ShowTag   VARCHAR(50) = '' 
  )
  AS
 BEGIN
    
    DECLARE @TABCOL VARCHAR(20)   --表字段
    DECLARE @FK     VARCHAR(20)   --外键
    DECLARE @PK     VARCHAR(20)   --主键
    DECLARE @CHECK  VARCHAR(20)   --约束
    DECLARE @INDEX  VARCHAR(20)   --索引
    DECLARE @ALL    VARCHAR(20)   --所有
  
     
    SELECT @TabCol = 'TABCOL'
    SELECT @FK     = 'FK'
    SELECT @PK     = 'PK'
    SELECT @CHECK  = 'CHECK'
    SELECT @INDEX  = 'INDEX'
    SELECT @ALL    = ''
  
 
    --字段名、字段序号、字段类型、长度、默认值、是否允许NULL、别名、是否标识列，计算列公式 
 
    IF (@ShowTag = @ALL OR @ShowTag = @TABCOL)    --表字段信息
    BEGIN
      IF Object_id('tempdb..#ccde') IS NOT NULL
        DROP TABLE #ccde
        
      SELECT Cast('' AS VARCHAR(20)) objname,
             Cast('' AS VARCHAR(20)) VALUE
      INTO   #ccde
      
      INSERT INTO #ccde
                  (objname,
                   VALUE)
      SELECT Convert(VARCHAR(20), objname) objname,
             Convert(VARCHAR(20), VALUE)   objname
      FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tableName, 'column', DEFAULT)
       
      --select * from syscolumns where id = object_id('wfpuser_a0113')
      --select * from  syscomments where id = object_id('wfpuser_a0113')       
      --select * from MASTER..systypes    
     
      SELECT a.[colid]                       [字段序号],
             a.[name]                        [字段名],
             b.[name]                        [类型],
             a.[length]                      [长度],
             REPLACE(REPLACE(CONVERT(VARCHAR(100), c.TEXT),'(',''),')','')              [默认值],
             CASE WHEN a.[isnullable] = 0 THEN '否' ELSE '是' END AS                    [是否允许NULL],
             ISNULL(d.[value], '')                                                      [别名],            
             CASE WHEN a.[status] = 0x80 THEN '是' ELSE '否' END AS                     [是否标识列], 
             REPLACE(REPLACE(ISNULL(CONVERT(VARCHAR(1000), e.TEXT), ''),'(',''),')','') [计算列公式]
             --Convert(INT, NULL)            isselect,
             --Convert(INT, NULL)            isupdate,
             --Convert(INT, NULL)            isinsert,
             --Convert(INT, NULL)            iswhere
      FROM   syscolumns a
             JOIN MASTER..systypes b
               ON a.xtype = b.xtype 
             LEFT JOIN syscomments c
               ON a.cdefault = c.id
             LEFT JOIN #ccde d
               ON a.name = d.objname
             LEFT JOIN syscomments e
               ON a.id = e.id AND a.colid = e.number  
      WHERE  a.id = Object_id(@tableName) 
      ORDER BY a.colid
      
    END
    
    IF (@ShowTag = @ALL) OR (@ShowTag = @FK)     --外键信息
    BEGIN
      select 
        oSub.name  AS   [子表名称],
        fk.name AS      [外键名称],
        SubCol.name AS  [子表列名],
        oMain.name  AS  [主表名称],
        MainCol.name AS [主表列名]
      from 
        sys.foreign_keys fk  
          JOIN sys.all_objects oSub  
              ON (fk.parent_object_id = oSub.object_id)
          JOIN sys.all_objects oMain 
              ON (fk.referenced_object_id = oMain.object_id)
          JOIN sys.foreign_key_columns fkCols 
              ON (fk.object_id = fkCols.constraint_object_id)
          JOIN sys.columns SubCol 
              ON (oSub.object_id = SubCol.object_id  
                  AND fkCols.parent_column_id = SubCol.column_id)
          JOIN sys.columns MainCol 
              ON (oMain.object_id = MainCol.object_id  
                  AND fkCols.referenced_column_id = MainCol.column_id) 
      WHERE oSub.name =@tableName OR oMain.name =@tableName
    END
    
    IF @ShowTag = @ALL OR @ShowTag = @PK         --主键信息
    BEGIN
      SELECT
        tab.name AS [表名],
        idx.name AS [主键名称],
        col.name AS [主键列名]
      FROM
        sys.indexes idx
          JOIN sys.index_columns idxCol 
            ON (idx.object_id = idxCol.object_id 
                AND idx.index_id = idxCol.index_id 
      AND idx.is_primary_key = 1)
          JOIN sys.tables tab
            ON (idx.object_id = tab.object_id)
          JOIN sys.columns col
            ON (idx.object_id = col.object_id
                AND idxCol.column_id = col.column_id)
      WHERE tab.name = @tableName 
    END
    
    IF @ShowTag = @ALL OR @ShowTag = @CHECK     --约束信息
    BEGIN
      SELECT
        tab.name AS [表名],
        idx.name AS [约束名称],
        col.name AS [约束列名]
      FROM
        sys.indexes idx
          JOIN sys.index_columns idxCol 
            ON (idx.object_id = idxCol.object_id 
                AND idx.index_id = idxCol.index_id 
                AND idx.is_unique_constraint = 1)
          JOIN sys.tables tab
            ON (idx.object_id = tab.object_id)
          JOIN sys.columns col
            ON (idx.object_id = col.object_id
                AND idxCol.column_id = col.column_id)
      WHERE tab.name = @tableName    
    END
    
    IF @ShowTag = @ALL OR @ShowTag = @INDEX     --索引信息
    BEGIN
      SELECT Object_name(i.object_id)                           [表名],      --name 
             Index_col(Object_name(i.object_id), i.index_id, 1) [索引字段],  --keys     
             i.name                                             [索引名称],
             CASE
               WHEN i.index_id = 1 THEN 'clustered'
               ELSE 'nonclustered'
             END                                                [索引类型]   --index_description  
      FROM   sys.indexes i
             JOIN sys.stats s
               ON i.object_id = s.object_id AND i.index_id = s.stats_id
      WHERE Object_name(i.object_id) = @tableName     
    END            
  END
  
 
 go
 EXECUTE MASTER..Sp_ms_marksystemobject 'sp_helptableinfo'
 go