获得SQLSERVER数据库表结构的SQL语句

我也忘了从哪里搞到的了,用了很长时间了,非常好用，希望能给大家帮助
可以得到表的字段类型,长度,是否非空,主键,注释等等

declare @table_name varchar(50)
set @table_name = 'ctn_manager_random_fee'  在这里改表名
select table_name '表名',column_basic.column_name '列名',column_default '默认值',is_nullable '允许为空',data_type '类型',character_maximum_length '长度',column_explain '注释',case when isnull(column_key.column_name,'') <> '' then 'YES' else '' end as '主键' 
from
  (
    select table_name,column_name ,column_default ,is_nullable ,data_type ,character_maximum_length,ordinal_position
    from   INFORMATION_SCHEMA.COLUMNS
    where  table_name = @table_name
  ) column_basic
  left join
  (
    SELECT objname as column_name,value as column_explain
    FROM   ::FN_LISTEXTENDEDPROPERTY(default,'user','dbo','table',@table_name,'column',default)
   ) column_explain on
  column_basic.column_name = column_explain.column_name
  left join
  (
    select column_name
    from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    where  table_name = @table_name
   ) column_key on
column_basic.column_name = column_key.column_name
order by ordinal_position

转载于:https://www.cnblogs.com/cobain/archive/2008/01/18/1043709.html