查询系统表INFORMATION_SCHEMA.TABLES得到所有用户表结构：
SELECT T.TABLE_SCHEMA as [TableOwner],T.TABLE_NAME as [TableName],
C.COLUMN_NAME as [ColumnName],C.DATA_TYPE as [DataType],C.CHARACTER_MAXIMUM_LENGTH as [Size],
C.NUMERIC_PRECISION as [Precision],C.COLUMN_DEFAULT as [DefaultValue],C.IS_NULLABLE as [AllowNull]
                   
, COLUMNPROPERTY( OBJECT_ID(T.TABLE_NAME),C.COLUMN_NAME,'IsComputed') as  IsComputed
          
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C on T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_NAME NOT LIKE 'sys%'
AND T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_NAME <> 'dtproperties'
AND T.TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
ORDER BY T.TABLE_NAME, C.ORDINAL_POSITION
得到所有是Identity的COlUMN集合
SELECT T.TABLE_NAME as Table_Name,
IDENT_SEED(T.TABLE_NAME) as seed,
IDENT_INCR(T.TABLE_NAME)as increment,
C.Column_Name
FROM INFORMATION_SCHEMA.TABLES T
join information_schema.COLUMNS C on C.Table_Name = T.Table_Name
WHERE IDENT_SEED(T.TABLE_NAME) IS NOT NULL AND
ColumnProperty(OBJECT_ID(T.TABLE_NAME),C.COLUMN_NAME,'IsIdentity') = 1 AND
T.TABLE_NAME NOT LIKE 'sys%'
AND T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_NAME <> 'dtproperties'
AND T.TABLE_SCHEMA <> 'INFORMATION_SCHEMA'

得到某个表的所有约束和列的默认值的语句：
select 
t_obj.name     as TABLE_NAME
,c_obj.name    as CONSTRAINT_NAME
,com.text    as DEFAULT_CLAUSE
from sysobjects c_obj
join  syscomments com on  c_obj.id = com.id
join  sysobjects t_obj on c_obj.parent_obj = t_obj.id 
join    sysconstraints con on c_obj.id = con.constid
join  syscolumns col on t_obj.id = col.id
and con.colid = col.colid
where
c_obj.xtype = 'C' and t_obj.name ='YourTableName'