sqlserver查询脚本 所有表名、列名、注释、数据类型、最大程度、主键、是否可空
时间: 2023-07-03 22:08:47 浏览: 52
以下是一份 SQL Server 查询脚本,可以获取所有表名、列名、注释、数据类型、最大长度、主键、是否可空的信息:
```
SELECT
t.name AS TableName,
c.name AS ColumnName,
ep.value AS ColumnDescription,
CASE
WHEN ty.name IN ('nvarchar', 'varchar', 'nchar', 'char', 'ntext', 'text') AND c.max_length <> -1
THEN ty.name + '(' + CAST(c.max_length AS VARCHAR(255)) + ')'
WHEN ty.name IN ('decimal', 'numeric')
THEN ty.name + '(' + CAST(c.precision AS VARCHAR(255)) + ',' + CAST(c.scale AS VARCHAR(255)) + ')'
ELSE ty.name
END AS DataType,
c.max_length AS MaxLength,
CASE
WHEN pk.name IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS IsPrimaryKey,
CASE
WHEN c.is_nullable = 1 THEN 'Yes'
ELSE 'No'
END AS IsNullable
FROM
sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id
LEFT JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.indexes pk ON ic.object_id = pk.object_id AND ic.index_id = pk.index_id AND pk.is_primary_key = 1
LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'
ORDER BY
t.name, c.column_id
```
这个脚本使用了系统表 sys.columns、sys.tables、sys.types、sys.index_columns、sys.indexes 和 sys.extended_properties 来获取相关信息。其中,sys.extended_properties 表用于获取列的注释信息。
相关推荐
![application/msword](https://img-home.csdnimg.cn/images/20210720083327.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)