SQLServer list the columns and attributes of each table

To sort out a Database Help document, you may need to list the columns and their attributes of each table in the warehouse. This may be helpful when developing some interfaces or outsourcing them to other companies. If someone else needs to open SQLServerManagementStudio (SSMS) for one-by-one viewing, it is undoubtedly a torment. To solve this problem, consider using the System

To sort out a Database Help document, you may need to list the columns and their attributes of each table in the database. This may be helpful when developing some interfaces or outsourcing them to other companies. If you need someone else to open SQL Server Management Studio (SSMS) to view them one by one, it is undoubtedly a torment. To solve this problem, consider using the System

If you need to sort out a Database Help document, you may needListLibraryEachTable columns and theirAttribute. This may be helpful when developing some interfaces or outsourcing them to other companies. If you need someone else to open SQL Server Management Studio (SSMS) to view them one by one, it is undoubtedly a torment.

To solve this problem, you can consider using the system directory view: sys. tables, sys. all_columns, sys. types

Sys. tables:

Providing DatabaseEachThe data of a row corresponding to the table. Including user tables and system tables. The is_ms_shipped column indicates whether it is a system table. This is useful when you only need explicit user tables. You do not need to filter by type = 'U' in the sys. sysobject Compatibility View.

Sys. all_columns:

Each column of each object in the database returns a row, many of which are the same as sys. type. However, some columns can only be searched in sys. type.

Sys. types:

In this directory view, the storage system or user-defined data types and theirAttribute. What is required in this article is the name of the data type, which is not listed in sys. all_columns. At the same time, database sorting rules will affect sys. types, so the built-in types such as text, ntext, vachar (), char (), nvarchar (), and nchar () vary with databases.

If it is not necessary, it is best to query only the content of the current database rather than cross-database, because these views are based on a single database. Run the following statement:

USE AdventureWorksGOSELECT OBJECT_SCHEMA_NAME (T. [object_id], DB_ID () AS [architecture name], T. [name] AS [Table name], AC. [name] AS [column name], TY. [name] AS [system data type], TY. is_user_defined AS [whether to customize the type], -- 1 = user-defined type, 0 = SQL Server System data type AC. [max_length] [maximum length], AC. [precision] [precision], -- if the column contains a numerical value, it indicates the accuracy of the column; otherwise, it is 0 AC. [scale] [value range], -- if the column contains a value, it is the column's decimal place; otherwise it is 0 AC. [is_nullable] [whether to allow null], AC. [is_ansi_padded] [whether to use ANSI_PADDING] -- 1 = if the column is of the character, binary, or variable type, the column uses ANSI_PADDING ON to act FROM sys. [tables] as t inner join sys. [all_columns] ac on t. [object_id] = AC. [object_id] inner join sys. [types] ty on ac. [system_type_id] = TY. [system_type_id] and ac. [user_type_id] = TY. [user_type_id] where t. [is_ms_shipped] = 0 order by t. [name], AC. [column_id]

You can get:

For some reason, when you need to query the information of another database in another database, you need to perform hard encoding as follows to get the same result:

USE [master] GOSELECT OBJECT_SCHEMA_NAME (T. [object_id], DB_ID ('adventureworks') AS [architecture name], T. [name] AS [Table name], AC. [name] AS [column name], TY. [name] AS [system data type], TY. is_user_defined AS [whether to customize the type], -- 1 = user-defined type, 0 = SQL Server System data type AC. [max_length] [maximum length], AC. [precision] [precision], -- if the column contains a numerical value, it indicates the accuracy of the column; otherwise, it is 0 AC. [scale] [value range], -- if the column contains a value, it is the column's decimal place; otherwise it is 0 AC. [is_nullable] [whether to allow null], AC. [is_ansi_padded] [whether to use ANSI_PADDING] -- 1 = if the column is of the character, binary, or variable type, the column uses ANSI_PADDING ON behavior FROM AdventureWorks. sys. [tables] as t inner join AdventureWorks. sys. [all_columns] ac on t. [object_id] = AC. [object_id] inner join AdventureWorks. sys. [types] ty on ac. [system_type_id] = TY. [system_type_id] and ac. [user_type_id] = TY. [user_type_id] where t. [is_ms_shipped] = 0 order by t. [name], AC. [column_id]

Finally, the data can be exported to excel for use through these query results.