T-SQL 查询显示表定义? [英] T-SQL query to show table definition?

问题描述

显示完整定义(包括 SQL Server 表的索引和键)的查询是什么?我想要一个纯粹的查询 - 并且知道 SQL Studio 可以给我这个，但我经常在只有最简单的应用程序的狂野"计算机上，我没有安装工作室的权利.但 SQLCMD 始终是一个选项.

What is a query that will show me the full definition, including indexes and keys for a SQL Server table? I want a pure query - and know that SQL Studio can give this to me, but I am often on "wild" computers that have only the most bare-bones apps and I have no rights to install studio. But SQLCMD is always an option.

更新:我尝试过 sp_help，但只产生了一条显示名称、所有者、类型和 Created_Datetime 的记录.我还缺少 sp_help 的其他内容吗?

UPDATE: I have tried sp_help, but is just yields one record which shows Name, Owner, Type and Created_Datetime. Is there something else I am missing with sp_help?

这就是我所说的:

sp_help 机场

请注意，我确实想要定义表的 DDL.

Note that I really do want the DDL that defines the table.

推荐答案

返回 DDL 没有简单的方法.但是，您可以从信息架构视图和系统视图.

There is no easy way to return the DDL. However you can get most of the details from Information Schema Views and System Views.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customers'

SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes
WHERE [object_id] = OBJECT_ID('dbo.Customers')

这篇关于T-SQL 查询显示表定义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！