sqlserver2000获得数据库字段信息的脚本

2015-09-04 16:45:21 访问(1705) 赞(0) 踩(0)

SELECT  
d.id as tableId,
d.name as tableName,
isnull(f.value,'') as tableComment,
a.colid as DataColumnId,
a.colorder as DataColumnIndex,
a.name as DataColumnName,
isnull(g.value,'') as DataColumnComment,
(
case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (   
                          SELECT   name   FROM   sysindexes   WHERE   indid   in(   
                          SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   
                          )))   then   1   else   0   end
) as DataColumnIsPrimaryKey,
b.name as DataColumnType,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as SqlLength,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as sqlsize,   
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as sqlsizeend,
(case   when   a.isnullable=1   then   1 else   0   end) as AllowDBNull,
isnull(e.text,'') as SqlDefaultValue,
(case when un.DataColumnName is null then 0 else 1 end) as IsUnique,
un.UniqueName,
fkt.ForeignKeyTableId,
fkt.ForeignKeyTableUser,
fkt.ForeignKeyTableName,
fkt.ForeignKeyDataColumnId,
fkt.ForeignKeyDataColumnName 
FROM   syscolumns   a   
left   join   systypes   b   on   a.xtype=b.xusertype   
inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and   d.status>=0   
left   join   syscomments   e   on   a.cdefault=e.id   
left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid       
left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0 
left   join 
(
SELECT
O.id as tableId,
O.Name as tableName,
OU.name as UniqueName, 
C.colid as DataColumnId,
C.name as DataColumnName
FROM   sysindexes   IX,   sysindexkeys   IXK,   syscolumns   C,   sysobjects   OU,   sysobjects   O 
WHERE   IX.indid   NOT   IN(0,   255) 
AND   IX.id   =   IXK.id 
AND   IX.indid   =   IXK.indid 
AND   IX.id   =   c.id 
AND   IXK.colid   =   C.colid 
AND   OU.parent_obj   =   IX.id 
AND   OU.xtype   =   'UQ ' 
AND   OU.name   =   IX.name 
AND   O.xtype   =   'U ' 
AND   O.id   =   IX.id	
)  
un on a.id = un.tableId and a.colid = un.DataColumnId 
left join
(
select 
col.id as tableId,
col.colid as DataColumnId,
col.name as DataColumnName, 
f.constid,
f.rkeyid,
f.rkey,
rs.id as ForeignKeyTableId,
u.name as ForeignKeyTableUser,
rs.name as ForeignKeyTableName,
rc.colid as ForeignKeyDataColumnId,
rc.name as ForeignKeyDataColumnName 
from 
syscolumns col,
sysforeignkeys f,
sysobjects s,
sysobjects rs left join sysusers u on rs.uid = u.uid,
syscolumns rc     
where 
f.fkeyid=col.id     
and f.fkey=col.colid     
and s.xtype = 'F'
and s.id = f.constid
and s.parent_obj = col.id 
and rc.id = rs.id
and rc.id = f.rkeyid
and rc.colid = f.rkey 		
) fkt
on a.id = fkt.tableId and a.colid = fkt.DataColumnId 
where
1 = 1 
order by a.id,a.colorder