Code example for extracting information such as database table name and field name using SQL

Code example for extracting information such as database table name and field name using SQL

This article describes how to use SQL statements to extract the instance code of table names and field names of all tables in the database. The test is conducted in SQLserver. The specific content is as follows:

-- Select d. name as TableName,. colorder AS ColOrder,. name AS Name, COLUMNPROPERTY (. ID,. name, 'isidentity ') AS IsIdentity, case when exists (SELECT 1 FROM dbo. sysobjects WHERE Xtype = 'pk' AND Name IN (SELECT Name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys where id =. id and colid =. colid) THEN 1 ELSE 0 end as primary key, B. name AS type,. length AS [length],. xprec AS [precision],. xscale AS [decimal], case when. isnullable = 1 THEN 1 ELSE 0 end as [can be empty], ISNULL (E. text, '') AS [default value], ISNULL (G. [value], '') AS [description] FROM dbo. syscolumns a left outer join dbo. policypes B ON. xtype = B. xusertype inner join dbo. sysobjects d on. id = D. id and d. xtype = 'U' and d. name <> 'dtproperties' left outer join dbo. syscomments e on. cdefault = E. id left outer join dbo. sysproperties g on. id = G. id and. colid = G. smallid -- where d. name = 'tablename' -- if you are looking for a specified table, remove the comment from order by 1, 2.

------ Test in SQLserver 2005

-- Query the feature SELECT (case when. colorder = 1 then d. name else ''end) as table name. -- if the table name is the same, null a is returned. colorder as field number,. name as field name, (case when COLUMNPROPERTY (. id,. name, 'isidentity ') = 1 then' √ 'else' end) as identity, (case when (SELECT count (*) FROM sysobjects -- query the primary key WHERE (name in (SELECT name FROM sysindexes WHERE (id =. id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id =. id) AND (colid in (SELECT colid FROM syscolumns WHERE (id =. id) AND (name =. name) AND (xtype = 'pk')> 0 then' √ 'else' end) as primary key, -- Query primary key END B. name as type,. length as occupies the number of bytes, COLUMNPROPERTY (. id,. name, 'precision ') as length, isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) as decimal places, (case when. isnullable = 1 then' √ 'else' end) as allows null, isnull (e. text, '') as default value, isnull (g. [value], '') AS field description FROM syscolumns a left join policypes B on. xtype = B. xusertype inner join sysobjects d on. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' left join syscomments e on. cdefault = e. id left join sys. extended_properties g on. id = g. major_id AND. colid = g. minor_id -- where d. name = 'pe _ u_vallimess' -- the table to be queried order by. id,. colorder

------ Extract the field name of a table

SELECT (case when. colorder = 1 then d. name else ''end) n' table name',. colorder N 'field No. ',. name N 'field name', isnull (g. [value], '') AS n' field description ', (case when COLUMNPROPERTY (. id,. name, 'isidentity ') = 1 then' √ 'else' 'end) n', (case when (SELECT count (*) FROM sysobjectsWHERE (name in (SELECT nameFROM sysindexesWHERE (id =. id) AND (indid in (SELECT indidFROM sysindexkeysWHERE (id =. id) AND (colid in (SELECT codecomfrom syscolumnsWHERE (id =. id) AND (name =. name) AND (xtype = 'pk')> 0 then' √ 'else' end) N 'Primary key', B. name n' type ',. length N 'number of bytes occupied ', COLUMNPROPERTY (. id,. name, 'precision ') as n' length', isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) as n' decimal places ', (case when. isnullable = 1 then' √ 'else' end) N 'allowed null', isnull (e. text, '') n' default value: 'FROM syscolumns aleft join policypes bon. xtype = B. xusertypeinner join sysobjects don. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' left join syscomments eon. cdefault = e. idleft join sys. extended_properties gon. id = g. major_id AND. colid = g. minor_id -- where condition input table name where d. name = 'consumebillbody' order by object_name (. id),. colorder

Summary

The above is all the sample code for SQL extraction of database table names, field names, and other information. I hope it will be helpful to you. If you are interested, you can continue to refer to this site: SQL Injection principles and solutions code examples, MySQL table data deletion methods with foreign key constraints, and several important MySQL variables, if you have any questions, you can leave a message at any time. The editor will reply to you in a timely manner. Thank you for your support!