Table of Contents
Sys.tables:
Sys.all_columns:
Sys.types:
Home Database Mysql Tutorial SQLServer 列出每个表的列和属性

SQLServer 列出每个表的列和属性

Jun 07, 2016 pm 03:18 PM
sqlserver list Attributes data tidy need

当需要整理一个数据库帮助文档是,可能需要 列出 库中 每个 表的列及其 属性 。这可能在开发一些接口或者外包给别的公司时有帮助。如果需要别人打开SQL Server Management Studio (SSMS)来一个一个查看,无疑是一种折磨。 解决这个问题可以考虑使用系统的目

       当需要整理一个数据库帮助文档是,可能需要列出库中每个表的列及其属性。这可能在开发一些接口或者外包给别的公司时有帮助。如果需要别人打开SQL Server Management Studio (SSMS)来一个一个查看,无疑是一种折磨。


       解决这个问题可以考虑使用系统的目录视图:sys.tables、sys.all_columns、sys.types


Sys.tables:


       提供数据库中每个表对应的一行数据。包括用户表和系统表。而其中的is_ms_shipped列,代表是否为系统表。这在你需要仅仅显式用户表的时候很有用。而不需要在sys.sysobject兼容性视图中通过type=’U’来筛选。


Sys.all_columns:


       数据库每一个对象的每一列都会返回一行,很多列和sys.type是相同的。但是有些列只能在sys.type中查找。


Sys.types:


      此目录视图存储系统或者用户自定义数据类型及它们的属性。本文中所需的是数据类型的名字,这列在sys.all_columns中是没有的。同时数据库的排序规则会影响sys.types,所以对于系统内置类型如text,ntext,vachar(),char(),nvarchar(),nchar()会因为数据库不同而不同。


       如非必须,最好只查询当前数据库的内容而不要跨数据库,因为这些视图是基于单个数据库的。运行以下语句:


USE AdventureWorks
GO
SELECT  OBJECT_SCHEMA_NAME(T.[object_id], DB_ID()) AS [架构名] ,
        T.[name] AS [表名] ,
        AC.[name] AS [列名] ,
        TY.[name] AS [系统数据类型] ,
        TY.is_user_defined AS [是否用户自定义类型],--1 = 用户定义类型,0 = SQL Server 系统数据类型
        AC.[max_length] [最大长度],
        AC.[precision] [精确度],--如果列包含的是数值,则为该列的精度;否则为0
        AC.[scale] [数值范围],--如果列包含的是数值,则为列的小数位数;否则为0
        AC.[is_nullable] [是否允许为空],
        AC.[is_ansi_padded][是否使用ANSI_PADDING]--1 = 如果列为字符、二进制或变量类型,则该列使用ANSI_PADDING ON 行为
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]
Copy after login



可以得到:


SQLServer 列出每个表的列和属性


由于某些原因需要在别的库上查询另外一个库的信息时,需要硬编码,如下,可以得到相同的结果:


USE [master] 
GO
SELECT  OBJECT_SCHEMA_NAME(T.[object_id], DB_ID('AdventureWorks')) AS [架构名] ,
        T.[name] AS [表名] ,
        AC.[name] AS [列名] ,
        TY.[name] AS [系统数据类型] ,
        TY.is_user_defined AS [是否用户自定义类型],--1 = 用户定义类型,0 = SQL Server 系统数据类型
        AC.[max_length] [最大长度],
        AC.[precision] [精确度],--如果列包含的是数值,则为该列的精度;否则为0
        AC.[scale] [数值范围],--如果列包含的是数值,则为列的小数位数;否则为0
        AC.[is_nullable] [是否允许为空],
        AC.[is_ansi_padded][是否使用ANSI_PADDING]--1 = 如果列为字符、二进制或变量类型,则该列使用ANSI_PADDING ON 行为
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]
Copy after login



最后,通过这些查询结果,可以把数据导出到excel里面供使用。
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem that the object named already exists in the sqlserver database How to solve the problem that the object named already exists in the sqlserver database Apr 05, 2024 pm 09:42 PM

How to solve the problem that the object named already exists in the sqlserver database

How to import mdf file into sqlserver How to import mdf file into sqlserver Apr 08, 2024 am 11:41 AM

How to import mdf file into sqlserver

What to do if the sqlserver service cannot be started What to do if the sqlserver service cannot be started Apr 05, 2024 pm 10:00 PM

What to do if the sqlserver service cannot be started

Where is the sqlserver database? Where is the sqlserver database? Apr 05, 2024 pm 08:21 PM

Where is the sqlserver database?

How to recover accidentally deleted database in sqlserver How to recover accidentally deleted database in sqlserver Apr 05, 2024 pm 10:39 PM

How to recover accidentally deleted database in sqlserver

How to check sqlserver port number How to check sqlserver port number Apr 05, 2024 pm 09:57 PM

How to check sqlserver port number

The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks Apr 29, 2024 pm 06:55 PM

The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks

How to delete sqlserver if the installation fails? How to delete sqlserver if the installation fails? Apr 05, 2024 pm 11:27 PM

How to delete sqlserver if the installation fails?

See all articles