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

This article mainly introduces the example analysis of Table dictionary data query SQL in SQL Server, which is very detailed and has certain reference value. Interested friends must read it!

Preface

It is written in the textbook on principles and Design of Database Systems (3rd Edition) as follows:

The database contains four types of data:

1. User data

two。 Meta data

3. Indexes

4. Apply metadata

Among them, metadata is also called data dictionary and is defined as follows:

The following article is going to share with you a SQL statement about querying the SQL Server Table structure.

T-SQL is as follows:

SELECT (case when a.colorder=1 then d.name else''end) table name, a.colorder field serial number, a.name field name, (case when a.colorder=1 then isnull (f. Valuememe') else''end) table description, (case when COLUMNPROPERTY (a.iddepartment a.namememery) = 1 then' 1'else'0' end) identification (case when (SELECT count (*) FROM sysobjectsWHERE (name in (SELECT name FROM sysindexesWHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeysWHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name) AND (xtype = 'PK')) primary key, b.name type, a.length occupies bytes, COLUMNPROPERTY (a.idmatia. Namee Last PRECISN') as length Isnull (COLUMNPROPERTY (a. 1'else a. Name), 0) as decimal places, (case whena.isnullable=1 then '1'else' 0' end) allows null, isnull (e.textpaper') default value, isnull (g. [value] '') AS [description] FROM syscolumns aleft join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtypewritten tables U' and d.name'dtproperties'left join syscomments e on a.cdefault=e.idleft join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.major_id and f.minor_id=0--where b.name is not nullWHERE d.nameplates database servers'- if only the specified table is queried Add this condition This case is table DBA_Serverorder by a. ID. A. Colorder.

The query results are as follows:

The above is all the contents of the article "sample Analysis of Table Dictionary data query SQL in SQL Server". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.