Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use three SQL views to find out all SQLServer database dictionaries". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "how to use three SQL views to find all SQLServer database dictionaries"!

1.SQLServer database dictionary-table structure .sql

SELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS table name, CASEWHENa.colorder=1THENisnull (f. ValueGravity') ELSE'' Endas table description, a.colorderAS field serial number, a.nameAS field name, CASEWHENCOLUMNPROPERTY (a.idmema.namemento IsIdentity') = 1THEN '√' ELSE''ENDAS ID 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 primary key, b.nameAS type, a.lengthAS length, COLUMNPROPERTY AS precision, ISNULL (COLUMNPROPERTY (a. ID AS a. Namemagery recording sequence scale, 0) AS decimal places, CASEWHENa.isnullable=1THEN' √' ELSE''ENDAS allows null, ISNULL (e.text '') AS default value, ISNULL (g. [value],'') AS field description, d.crdateAS creation time, CASEWHENa.colorder=1THENd.refdateELSENULLENDAS change time FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status > = 0LEFTOUTERJOINdbo.syscommentseONa.cdefaultaltere.idLEFTOUTERJOINdbo.syspropertiesgNa.idANDa.colidANDa.colidroomg.smallidANDg.nameplate configuration MSexamples DescriptionBo.smallidANDINdbo.syspropertiesfOd.Nidconstrucf.IDDf.smallidbread 0ANDf.nameexamples d.crdateAS DescriptionORDERBYnameName .colorderSqlServer2005 database dictionary-a.id-table structure CASEWHENa.colorder=1THENd.nameELSE''ENDAS table name, CASEWHENa.colorder=1THENisnull (f. Valuejournal') ELSE'' Endas table description, a.colorderAS field serial number, a.nameAS field name, CASEWHENCOLUMNPROPERTY (a.idmema.namememery a.nameAS IsIdentity') = 1then '√' ELSE''ENDAS identity, 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 primary key B.nameAS type, a.lengthAS length, COLUMNPROPERTY AS precision, ISNULL (COLUMNPROPERTY (a.namememe. Playbook sequence Scale'), 0) AS decimal places, CASEWHENa.isnullable=1THEN' √ 'ELSE''ENDAS allows null, ISNULL (e.texttextbook') AS default value, ISNULL (g. [value],') AS field description, d.crdateAS creation time CASEWHENa.colorder=1THENd.refdateELSENULLENDAS change time 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, field serial number

How to use three SQL views to find out all SQLServer database dictionaries

2.SQLServer database dictionary-index .sql SELECTTOP100PERCENT--a.id,CASEWHENb.keyno=1THENc.nameELSE''ENDAS table name, CASEWHENb.keyno=1THENa.nameELSE''ENDAS index name, d.nameAS column name, b.keynoAS index order, CASEindexkey_property (c. Idforce b. Indid, b. Keynoreline) WHEN1THEN' descending 'WHEN0THEN' ascending order' ENDAS sort, CASEWHENp.idISNULLTHEN''ELSE' √ 'ENDAS primary key, CASEINDEXPROPERTY (c. Idjue. Name) 'IsClustered') WHEN1THEN' √' WHEN0THEN''ENDAS aggregates, CASEINDEXPROPERTY (c.idmeme. Namethschems IsUnique`) WHEN1THEN' √ 'WHEN0THEN''ENDAS unique, CASEWHENe.idISNULLTHEN''ELSE' √' ENDAS unique constraint, a.OrigFillFactorAS fill factor, c.crdateAS creation time C.refdateAS change time 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.idrect Noble IsUserTable') = 1) AND (OBJECTPROPERTY (a.idmenIsIsShipped') = 0) AND (INDEXPROPERTY (a.idmema.nametemery) = 0) ORDERBYc.name,a.name,b.keyno

3.SQLServer database dictionary-tables. View. Function. Stored procedure. Trigger. Primary key. Foreign key. Restrain. Rule .sql

SELECTDISTINCTTOP100PERCENTisnull (p.name _ reference') AS parent object, o.xtype CASEo.xtypeWHEN'C'THEN'CHECK constraint 'WHEN'D'THEN' default value or DEFAULT constraint' WHEN'F'THEN'FOREIGNKEY constraint 'WHEN'L'THEN' Log' WHEN'FN'THEN' Scalar function 'WHEN'IF'THEN' embedded Table function' WHEN'P'THEN' stored procedure 'WHEN'PK'THEN'PRIMARYKEY constraint' WHEN'RF'THEN' replication filter stored procedure 'WHEN'S'THEN' system Table' WHEN'TF'THEN' Table function 'WHEN'TR'THEN' trigger' WHEN'U'THEN' user Table 'WHEN'UQ'THEN'UNIQUE constraint' WHEN'V'THEN' View 'WHEN'X'THEN' extended stored procedure' WHEN'R'THEN' Rule 'ELSENULLENDAS type O.nameAS object name, o.crdateAS creation time, o.refdateAS change time, c.textAS declaration statement, OBJECTPROPERTY (o.xtypeIN) FROMdbo.sysobjectsoLeftJOINdbo.sysobjectspONo.parent_obj=p.idLEFTOUTERJOINdbo.syscommentscONo.id=c.idWHERE-- (o.xtypeIN) 'V')) AND (OBJECTPROPERTY (O.idMagneIsMSShipped') = 0) AND (isnull (p.namemagem') Naturdtproperties`). At this point, I believe you have a deeper understanding of "how to use three SQL views to find out all SQLServer database dictionaries". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.