Sql获取表结构的描述与缺省值

--获取数据库中的所有表

SELECT name FROM sysobjects WHERE type = 'U'

--获取SqlServer中表结构 主键，及描述

declare @table_name as varchar(max)
set @table_name = 'tableName' --表名自己改
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(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=@table_name order by sys.columns.column_id

--获取缺省值

select table_no=id,Field_en=name,Field_cn= ' ',
field_type=(select top 1 name from systypes where systypes.xtype=a.xtype),
is_null=case isnullable when 1 then 'True ' else 'False ' end,
is_key=case (select top 1 indid from sysindexkeys where colid = a.colid and id=a.id) when 1 then 'True ' else 'False ' end,
default_value=(select top 1 text from syscomments where id=cdefault),
var_a=length,var_b=xprec,var_c=xscale from syscolumns a
where id = object_id( 'tableName ' ) order by id --表名自己改

--合二为一

DECLARE @tableName VARCHAR(100)
SET @tableName = 'tableName' -- set table name
SELECT t1.Field_en AS [Column Name],
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 = 'uniqueidentifier' OR t1.field_type = 'xml' THEN
t1.field_type
ELSE t1.field_type + '(' + CONVERT(VARCHAR, t1.var_a, 10) + ')'
END AS [Data Type],
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 [Default Value],
CASE
WHEN t1.is_null != 'False' THEN ''
ELSE 'NOT NULL'
END AS [IS Null],
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