[ת]SQL 2005ÖÐ×Ô¶¯Éú³ÉÊý¾Ý×Öµä

create proc [dbo].[GenerateDataDictionary] as
begin
--»ñÈ¡Êý¾Ý±íÃû
declare  @tableid int
declare mycursor Cursor
for select object_id from sys.objects where type='U' and name<>'dtproperties'
--»ñÈ¡×Ö¶ÎÃû³Æ¡¢±êʶ¡¢×Ö¶ÎÐòºÅ¡¢Õ¼ÓÃ×Ö½ÚÊý¡¢Ð¡ÊýλÊý¡¢ÔÊÐí¿ÕµÈ
open mycursor
fetch next from mycursor into @tableid
while(@@fetch_status=0)
begin
print '±íÃû£º'+object_name(@tableid)
select
        col.colorder ×Ö¶ÎÐòºÅ,
        col.name ×Ö¶ÎÃû,
        t.name ÀàÐÍ,
        col.length Õ¼ÓÃ×Ö½ÚÊý,
COLUMNPROPERTY(col.id,col.name,'PRECISION') as ³¤¶È,
isnull(COLUMNPROPERTY(col.id,col.name,'Scale'),0) as СÊýλÊý,
        (case when
            (SELECT count(*)
from sysobjects
WHERE (name in
                    (SELECT name
from sysindexes
WHERE (id = col.id)
AND (indid in
                                    (SELECT indid
from sysindexkeys
WHERE (id = col.id) AND (colid in
                                            ( SELECT colid
from syscolumns
WHERE (id = col.id)
AND (name = col.name)