资源描述：
《sql2008通过sql输出表结构到excel【全】》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、【参考并修改】sql2008通过sql输出表结构到EXCEL【全】http://blog.csdn.net/kevinkjf/article/details/8021716上周需要做个导出sqlserver2008数据库的表结构到EXCEL的需求，考虑到数据库结构还会改变，也考虑到以后的复用，于是查了些资料，写了一个生成表结构的脚本，较之网上找到的版本加入了外键，外键参考表，外键参考列，是否唯一等字段，sql如下：SELECTTABLENAME=CaseWhenA.colorder=1ThenD.nameElse''End
2、,NO=A.colorder,COLUMN_NAME=A.name,COLUMN_DESC=cast(isnull(G.[value],'')asvarchar(100)),IS_IDENTITY=CaseWhenCOLUMNPROPERTY(A.id,A.name,'IsIdentity')=1Then'√'Else''End,PK=CaseWhenexists(SELECT1FROMsysobjectsWherextype='PK'andparent_obj=A.idandnamein(SELECTnameFROMsy
3、sindexesWHEREindidin(SELECTindidFROMsysindexkeysWHEREsysindexkeys.id=A.idANDsysindexkeys.colid=A.colid)))then'√'else''end,FK=CaseWhenexists(select1fromsysforeignkeyswherefkeyid=A.idandfkey=A.colidandconstidin(selectdistinct(id)fromsysobjectswhere--OBJECT_NAME(pare
4、nt_obj)='T_USER'--andxtype='F'))then'√'else''end,REFER_TABLE=cast(isnull((selectrtableNamefrom(selectdistinct(t2.rtableName)from(selectcol.name,f.constidastempfromsyscolumnscol,sysforeignkeysfwheref.fkeyid=col.idandf.fkey=col.colidandf.constidin(selectdistinct(id)
5、fromsysobjectswhere--OBJECT_NAME(parent_obj)='T_USER'--andxtype='F'andfkeyid=A.id))ast1,(selectOBJECT_NAME(f.rkeyid)asrtableName,col.name,f.constidastempfromsyscolumnscol,sysforeignkeysfwheref.rkeyid=col.idandf.rkey=col.colidandf.constidin(selectdistinct(id)fromsy
6、sobjectswhere--OBJECT_NAME(parent_obj)='T_USER'--andxtype='F'andfkeyid=A.id))ast2wheret1.temp=t2.tempandt1.name=A.name)fkResult1),'')asvarchar(100)),REFER_COL=cast(isnull((selectnamefrom(selectdistinct(t2.rtableName),t2.namefrom(selectcol.name,f.constidastempfroms
7、yscolumnscol,sysforeignkeysfwheref.fkeyid=col.idandf.fkey=col.colidandf.constidin(selectdistinct(id)fromsysobjectswhere--OBJECT_NAME(parent_obj)='T_USER'--andxtype='F'andfkeyid=A.id))ast1,(selectOBJECT_NAME(f.rkeyid)asrtableName,col.name,f.constidastempfromsyscolu
8、mnscol,sysforeignkeysfwheref.rkeyid=col.idandf.rkey=col.colidandf.constidin(selectdistinct(id)fromsysobjectswhere--OBJECT_NAME(parent_obj)='T_USER'--and
