SQL Server lists the columns and properties for each table

Original: SQL Server lists columns and properties for each table

When you need to organize a database help document, you might want to list the columns and their properties for each table in the library. This may be helpful in developing some interfaces or outsourcing to other companies. If you need someone to open SQL Server Management Studio (SSMS) to one view, it is undoubtedly a torment.

To solve this problem, consider using the system's catalog view: Sys.tables, Sys.all_columns, sys.types

Sys.tables:

Provides a row of data for each table in the database. Includes user tables and system tables. And the is_ms_shipped column, which represents whether it is a system table. This is useful when you need only explicit user tables. You do not need to filter by type= ' U ' in sys.sysobject compatibility View.

Sys.all_columns:

Each column of each object in the database returns one row, and many columns and Sys.type are the same. However, some columns can only be found in Sys.type.

Sys.types:

This catalog view stores system or user-defined data types and their properties. 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 the collation of the database affects sys.types, so for system built-in types such as Text,ntext,vachar (), char (), nvarchar (), nchar () will vary depending on the database.

If it is not necessary, it is best to query the contents of the current database only, not across databases, because these views are based on a single database. Run the following statement:

Use Adventureworksgoselect  object_schema_name (t.[object_id], db_id ()) as [schema name],        t.[name] as [table name],        ac.[ Name] as [column name],        ty.[ Name] As [System data type],        ty.is_user_defined as [whether user-defined type],--1 = user-defined type, 0 = SQL Server system data type        ac.[max_length] [Maximum length],
   ac.[precision] [accuracy],--If the column contains a number, the precision of the column, otherwise 0        Ac.[scale] [numeric Range],--if the column contains a numeric value, it is the number of decimal digits of the column; otherwise 0        Ac.[is_ Nullable] [whether NULL is allowed],        ac.[is_ansi_padded][whether to use ansi_padding]--1 = If the column is a character, binary, or variable type, then the columns use the ANSI_PADDING on behavior 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] = 0ORDER by t.[name],        ac.[column_id]

Can get:

For some reason you need to query the other library for information on another library, you need to hard-code, as follows, you can get the same result:

Use [master] goselect  object_schema_name (t.[object_id], db_id (' AdventureWorks ')) as [schema name],        t.[name] as [table name], C2/>ac.[name] As [column name],        ty.[ Name] As [System data type],        ty.is_user_defined as [whether user-defined type],--1 = user-defined type, 0 = SQL Server system data type        ac.[max_length] [Maximum length],
   ac.[precision] [accuracy],--If the column contains a number, the precision of the column, otherwise 0        Ac.[scale] [numeric Range],--if the column contains a numeric value, it is the number of decimal digits of the column; otherwise 0        Ac.[is_ Nullable] [whether NULL is allowed],        ac.[is_ansi_padded][whether to use ansi_padding]--1 = If the column is a character, binary, or variable type, then the columns use the 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] = 0ORDER by t.[name],        ac.[column_id]

Finally, with these query results, you can export the data to Excel for use.

SQL Server lists the columns and properties for each table