我有一个SQL Server数据库,想知道它有哪些列和数据类型。我更倾向于通过查询来完成这个任务而不是使用Enterprise Manager等GUI工具。有没有一种方法可以做到这一点?
我有一个SQL Server数据库,想知道它有哪些列和数据类型。我更倾向于通过查询来完成这个任务而不是使用Enterprise Manager等GUI工具。有没有一种方法可以做到这一点?
SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, TC.CONSTRAINT_TYPE, C.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS AS C
FULL JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC ON C.COLUMN_NAME = CC.COLUMN_NAME
FULL JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE C.TABLE_NAME = '<Table Name>';
如果您正在使用 Brent Ozar 团队的 FirstResponderKit,您也可以运行此查询:
exec sp_blitzindex @tablename='MyTable'
CREATE PROCEDURE [dbo].[describe]
(
@SearchStr nvarchar(max)
)
AS
BEGIN
SELECT
CONCAT([COLUMN_NAME],' ',[DATA_TYPE],' ',[CHARACTER_MAXIMUM_LENGTH],' ',
(SELECT CASE [IS_NULLABLE] WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END),
(SELECT CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '' ELSE CONCAT(' DEFAULT ',[COLUMN_DEFAULT]) END)
) AS DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE @SearchStr
END
info()
函数的输出。USE [Database_Name]
IF OBJECT_ID('tempdo.dob.#primary_key', 'U') IS NOT NULL DROP TABLE #primary_key
SELECT
CONS_T.TABLE_CATALOG,
CONS_T.TABLE_SCHEMA,
CONS_T.TABLE_NAME,
CONS_C.COLUMN_NAME,
CONS_T.CONSTRAINT_TYPE,
CONS_T.CONSTRAINT_NAME
INTO #primary_key
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONS_T
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CONS_C ON CONS_C.CONSTRAINT_NAME= CONS_T.CONSTRAINT_NAME
SELECT
SMA.name AS [Schema Name],
ST.name AS [Table Name],
SC.column_id AS [Column Order],
SC.name AS [Column Name],
PKT.CONSTRAINT_TYPE,
PKT.CONSTRAINT_NAME,
SC.system_type_id,
STP.name AS [Data Type],
SC.max_length,
SC.precision,
SC.scale,
SC.is_nullable,
SC.is_masked
FROM sys.tables AS ST
JOIN sys.schemas AS SMA ON SMA.schema_id = ST.schema_id
JOIN sys.columns AS SC ON SC.object_id = ST.object_id
JOIN sys.types AS STP ON STP.system_type_id = SC.system_type_id
LEFT JOIN #primary_key AS PKT ON PKT.TABLE_SCHEMA = SMA.name
AND PKT.TABLE_NAME = ST.name
AND PKT.COLUMN_NAME = SC.name
ORDER BY ST.name ASC, SMA.name ASC