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

This article mainly introduces "how to use SQL statements to view the structural information of SQL Server". In daily operation, I believe that many people have doubts about how to use SQL statements to view the structural information of SQL Server. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how to use SQL statements to view the structural information of SQL". Next, please follow the editor to study!

Get database and configuration information

1. Get database configuration information

Exec sp_server_info

2. Return all databases

Exec sp_databases acquires the user table in the system

There are three methods that can be used:

1. Use sysobjects system table or sys.objects catalog view

Select * from sysobjects where xtype='U' select * from sys.objects where xtype='U'

2. Make use of sys.tables catalog view

Select * from sys.tables

3. Using stored procedure sp_tables

Exec sp_tables gets all the user views in the system

There are two methods that can be used, namely:

1. Use sysobjects system table or sys.objects catalog view

Select * from sysobjects where xtype='V' select * from sys.objects where xtype='V'

2. Make use of sys.tables catalog view

Select * from sys.tables gets all the user stored procedures in the database

There are two methods that can be used, namely:

1. Use sysobjects system table or sys.objects catalog view

Select * from sysobjects where xtype='P' select * from sys.objects where xtype='P'

2. Make use of sys.procedures catalog view

Select * from sys.procedures gets the definition script for the stored procedure or view

To use the system views sys.all_objects and sys.sql_modules, write as follows:

Select a.namejold a. [type], b. [definition] from sys.all_objects a dint sys. Sqltogether modules b where a.is_ms_shipped=0 and a.object_id = b.object_id and a. [type] in ('packs reynchary V') order by a. [name] asc

Sys.all_objects is the system view in sql server 2012, and in earlier sql server versions, sys.objects should be used. at the same time, sys.objects is also available in 2012, but for subsequent compatibility, it is better to use the new name in the new version.

The common types of sys.all_objects.type fields are: P = stored procedure, V = view, U = table (user-defined type), TT = table type, AF = aggregate function, and so on.

What sys.sql_modules.definition returns is the script that defines the stored procedure or view.

Get the field information of the table

1. If you simply get the field name, you only need to use the system table syscolumns.

Select * from syscolumns where id=object_id ('table name')

2. If you want to get fields and corresponding data types, you need to use system tables syscolumns and systypes.

Select a.name as [column], b.name as type from syscolumns adronyypes b where a.id=object_id ('table name') and a.xtype=b.xtype

3. Get more detailed table structure information, and call [INFORMATION_SCHEMA]. [COLUMNS].

SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.NUMERIC_PRECISION, c.NUMERIC_SCALE FROM [INFORMATION_SCHEMA]. [COLUMNS] c WHERE TABLE_NAME = 'idata'

At this point, the study on "how to use SQL statements to view the structural information of SQL Server" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.