Shulou(Shulou.com)06/01 Report--

This article introduces the example code of using SQL statement to extract the table names and field names of all tables in the database, and tests them in SQLserver. The details are as follows:

-- query the characteristics of all fields in all user tables: SELECT D.Name as TableName, A.colorder AS ColOrder, A.name AS Name, COLUMNPROPERTY (A. IDJ A.Name, 'IsIdentity') AS IsIdentity, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysobjects WHERE Xtype =' PK' AND Name IN (SELECT indid FROM sysindexkeys WHERE ID = A.ID AND colid = A.colid)) THEN 1 ELSE 0 END AS primary key, B.name AS type, A.length AS [length] A.xprec AS [precision], A.xscale AS [decimal], CASE WHEN A.isnullable = 1 THEN 1 ELSE 0 END AS [can be nullified], ISNULL (E.text,') AS [default], ISNULL (G. [value] '') AS [description] FROM dbo.syscolumns A LEFT OUTER JOIN dbo.systypes B ON A.xtype = B.xusertype INNER JOIN dbo.sysobjects D ON A.id = D.id AND D.xtype ='U' AND D.name 'dtproperties' LEFT OUTER JOIN dbo.syscomments E ON A.cdefault = E.id LEFT OUTER JOIN dbo.sysproperties G ON A.id = G.id AND A.colid = G.smallid--WHERE D.Nameplate tablename`-if you find the specified table, remove ORDER BY 1,2

-tested in SQLserver 2005

-- query the characteristic SELECT (case when a.colorder=1 then d.name else''end) as table names of all fields in all user tables. If the table names are the same, return the empty a.colorder as field serial number, a.name as field name, (case when COLUMNPROPERTY (a.iddepartment a.name,' IsIdentity') = 1 then '√' else''end) as ID. (case when (SELECT count (*) FROM sysobjects-- query primary key WHERE (name in (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns)) WHERE (id = a.id) AND (name = a.name)) AND (xtype = 'PK')) > 0 then' √ 'else' end) as primary key -- query primary key END b.name as type, number of bytes occupied by a.length as, length of COLUMNPROPERTY (a.idrect a.namememery recording PRECISION') as, isnull (COLUMNPROPERTY (a.idgramma a.namerecoery recording Scale'), 0) as decimal places, (case when a.isnullable=1 then '√' else 'end) as allows null, isnull (e.textcoding') as default value, isnull (g. [value]) '') the AS field describes the FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtypewritten characters U' and d.name 'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id-- where d.nameplates performing performing messages ValliMessage'-- the table to be queried, order by a.idjie a.colorder.

-extract the field name of a table

SELECT (case when a.colorder=1 then d.name else''end) N' table name', a.colorder N' field serial number', a.name N' field name', isnull (g. [value],') AS N' field description', (case when COLUMNPROPERTY (a. (case when (SELECT count (*) FROM sysobjectsWHERE (name in (id = a.id) AND (indid in (SELECT indidFROM sysindexkeysWHERE (id = a.id) AND (colid in (SELECT colidFROM syscolumnsWHERE (id = a.id) AND (name = a.name) AND (xtype = 'PK')) > 0 then' √ 'else' end) N' Primary key', b.name N' Type', a.length N' occupied Bytes', COLUMNPROPERTY (a.iddline a.name) 'PRECISION') as N' length', isnull (COLUMNPROPERTY (a. ID. A. Name paper recording scale), 0) as N 'decimal places', (case when a.isnullable=1 then '√' else''end) N' allow null', isnull (e.text) '') N' default 'FROM syscolumns aleft join systypes bon a.xtype=b.xusertypeinner join sysobjects don a.id=d.id and d.xtypewritten entries U' and d.name'dtproperties'left join syscomments eon a.cdefault=e.idleft join sys.extended_properties gon a.id=g.major_id AND a.colid = g.minor_id--where condition input table name where d.name='ConsumeBillBody'order by object_name (a.id), a.colorder

Summary

The above is the whole content of the SQL extraction database table name and field name and other information code examples. I hope it will be helpful to you. Interested friends can continue to refer to this site: SQL injection principle and solution code example, MySQL delete table data with foreign key constraints method introduction, several more important MySQL variables, etc., if you have any questions, you can leave a message at any time, Xiaobian will reply you in time. Thank you for your support to this site!

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.