表值函数 fn_getTabStru ——获取某个表的表结构

来源：互联网发布：部落冲突刷钻石软件编辑：程序博客网时间：2024/04/23 14:02
原来写过一个存储过程，不过现在觉得还是表值函数更方便一些，特别是在批量处理表中的列时……
IF OBJECT_ID('fn_getTabStru') IS NOT NULL  BEGIN      DROP FUNCTION fn_getTabStru  END  GO  CREATE FUNCTION fn_getTabStru   (         @tableName VARCHAR(100)  )  RETURNS TABLE   AS  RETURN(SELECT t1.Field_en AS [ColumnName],             CASE                   WHEN t1.field_type = 'int' OR t1.field_type = 'smallint' OR t1.field_type                       = 'datetime' OR t1.field_type = 'tinyint' OR t1.field_type                        = 'bit'                       OR t1.field_type = 'date' OR t1.field_type = 'image'                       OR t1.field_type = 'money' OR t1.field_type = 'text'                       OR t1.field_type = 'ntext' OR t1.field_type = 'real'                       OR t1.field_type = 'smalldatetime' OR t1.field_type =                        'smallmoney'                       OR t1.field_type = 'timestamp' OR t1.field_type =                        'sql_variant' OR t1.field_type='bigint'                     OR t1.field_type = 'uniqueidentifier' OR t1.field_type =                        'xml' THEN t1.field_type                WHEN t1.field_type = 'nvarchar' OR t1.field_type = 'nchar' THEN t1.field_type + '(' + CONVERT(VARCHAR, t1.var_a/2, 10) + ')'                ELSE t1.field_type + '(' + CONVERT(VARCHAR, t1.var_a, 10) + ')'             END AS [DataType],             CASE                   WHEN t1.default_value IS NULL THEN ''                  WHEN t1.default_value = 'getdate()' OR t1.default_value =                       '(getdate())' THEN 'getdate()'                  ELSE REPLACE(REPLACE(t1.default_value, '(', ''), ')', '')             END AS [DefaultValue],             CASE                   WHEN t1.is_null != 'False' THEN ''                  ELSE 'NOT NULL'             END AS [ISNull],             ISNULL(t2.[DESCRIPTION], '') AS [Description]      FROM   (                 SELECT Field_en = NAME,                        field_type = (                            SELECT TOP 1 NAME                            FROM   systypes                            WHERE  systypes.xtype = a.xtype                        ),                        var_a = length,                        is_null = CASE isnullable                                       WHEN 1 THEN 'True '                                       ELSE 'False '                                  END,                        default_value = (                            SELECT TOP 1 TEXT                            FROM   syscomments                            WHERE  id = cdefault                        )                 FROM   syscolumns a                 WHERE  id = OBJECT_ID(@tableName)             ) AS t1             LEFT JOIN (                      SELECT DISTINCT(sys.columns.name),                             (                                 SELECT VALUE                                 FROM   sys.extended_properties                                 WHERE  sys.extended_properties.major_id = sys.columns.object_id                                        AND sys.extended_properties.minor_id = sys.columns.column_id                             ) AS DESCRIPTION                      FROM   sys.columns,                             sys.tables,                             sys.types                      WHERE  sys.columns.object_id = sys.tables.object_id                             AND sys.columns.system_type_id = sys.types.system_type_id                             AND sys.tables.name = @tableName                  ) AS t2                  ON  t1.Field_en = t2.name  )
0 0