本篇內容主要講解“如何利用三個SQL視圖查出所有SQLServer數據庫字典”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“如何利用三個SQL視圖查出所有SQLServer數據庫字典”吧!

成都創新互聯公司是一家專注于成都做網站、網站設計與策劃設計,垣曲網站建設哪家好?成都創新互聯公司做網站,專注于網站建設十年,網設計領域的專業建站公司;建站業務涵蓋:垣曲等地區。垣曲做網站價格咨詢:18980820575

1.SQLServer數據庫字典--表結構.sql

SELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表說明,a.colorderAS字段序號,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS標識,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主鍵,b.nameAS類型,a.lengthAS長度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小數位數,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允許空,ISNULL(e.text,'')AS默認值,ISNULL(g.[value],'')AS字段說明,d.crdateAS創建時間,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改時間FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOINdbo.syspropertiesgONa.id=g.idANDa.colid=g.smallidANDg.name='MS_Description'LEFTOUTERJOINdbo.syspropertiesfONd.id=f.idANDf.smallid=0ANDf.name='MS_Description'ORDERBYd.name,a.colorderSqlServer2005數據庫字典--表結構.sqlSELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表說明,a.colorderAS字段序號,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS標識,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主鍵,b.nameAS類型,a.lengthAS長度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小數位數,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允許空,ISNULL(e.text,'')AS默認值,ISNULL(g.[value],'')AS字段說明,d.crdateAS創建時間,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改時間FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOINsys.extended_propertiesgONa.id=g.major_idANDa.colid=g.minor_idANDg.name='MS_Description'LEFTOUTERJOINsys.extended_propertiesfONd.id=f.major_idANDf.minor_id=0ANDf.name='MS_Description'ORDERBYd.name,字段序號

怎樣利用三個SQL視圖查出所有SQLServer數據庫字典

2.SQLServer數據庫字典--索引.sql
　　SELECTTOP100PERCENT--a.id,CASEWHENb.keyno=1THENc.nameELSE''ENDAS表名,CASEWHENb.keyno=1THENa.nameELSE''ENDAS索引名稱,d.nameAS列名,b.keynoAS索引順序,CASEindexkey_property(c.id,b.indid,b.keyno,'isdescending')WHEN1THEN'降序'WHEN0THEN'升序'ENDAS排序,CASEWHENp.idISNULLTHEN''ELSE'√'ENDAS主鍵,CASEINDEXPROPERTY(c.id,a.name,'IsClustered')WHEN1THEN'√'WHEN0THEN''ENDAS聚集,CASEINDEXPROPERTY(c.id,a.name,'IsUnique')WHEN1THEN'√'WHEN0THEN''ENDAS,CASEWHENe.idISNULLTHEN''ELSE'√'ENDAS約束,a.OrigFillFactorAS填充因子,c.crdateAS創建時間,c.refdateAS更改時間FROMdbo.sysindexesaINNERJOINdbo.sysindexkeysbONa.id=b.idANDa.indid=b.indidINNERJOINdbo.syscolumnsdONb.id=d.idANDb.colid=d.colidINNERJOINdbo.sysobjectscONa.id=c.idANDc.xtype='U'LEFTOUTERJOINdbo.sysobjectseONe.name=a.nameANDe.xtype='UQ'LEFTOUTERJOINdbo.sysobjectspONp.name=a.nameANDp.xtype='PK'WHERE(OBJECTPROPERTY(a.id,N'IsUserTable')=1)AND(OBJECTPROPERTY(a.id,N'IsMSShipped')=0)AND(INDEXPROPERTY(a.id,a.name,'IsAutoStatistics')=0)ORDERBYc.name,a.name,b.keyno

3.SQLServer數據庫字典--表.視圖.函數.存儲過程.觸發器.主鍵.外鍵.約束.規則.sql

SELECTDISTINCTTOP100PERCENTisnull(p.name,'')AS父對象,o.xtype,CASEo.xtypeWHEN'C'THEN'CHECK約束'WHEN'D'THEN'默認值或DEFAULT約束'WHEN'F'THEN'FOREIGNKEY約束'WHEN'L'THEN'日志'WHEN'FN'THEN'標量函數'WHEN'IF'THEN'內嵌表函數'WHEN'P'THEN'存儲過程'WHEN'PK'THEN'PRIMARYKEY約束'WHEN'RF'THEN'復制篩選存儲過程'WHEN'S'THEN'系統表'WHEN'TF'THEN'表函數'WHEN'TR'THEN'觸發器'WHEN'U'THEN'用戶表'WHEN'UQ'THEN'UNIQUE約束'WHEN'V'THEN'視圖'WHEN'X'THEN'擴展存儲過程'WHEN'R'THEN'規則'ELSENULLENDAS類型,o.nameAS對象名,o.crdateAS創建時間,o.refdateAS更改時間,c.textAS聲明語句,OBJECTPROPERTY(o.id,N'IsMSShipped')FROMdbo.sysobjectsoLeftJOINdbo.sysobjectspONo.parent_obj=p.idLEFTOUTERJOINdbo.syscommentscONo.id=c.idWHERE--(o.xtypeIN('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V'))AND(OBJECTPROPERTY(o.id,N'IsMSShipped')=0)AND(isnull(p.name,'')<>N'dtproperties')。

到此，相信大家對“如何利用三個SQL視圖查出所有SQLServer數據庫字典”有了更深的了解，不妨來實際操作一番吧！這里是創新互聯建站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

網站題目：如何利用三個SQL視圖查出所有SQLServer數據庫字典-創新互聯
網址分享：http://m.2m8n56k.cn/article42/dhpshc.html

成都網站建設公司_創新互聯，為您提供做網站、App設計、服務器托管、外貿建站、企業建站、網站策劃

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：[email protected]。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯