vista - Consulta del servidor SQL para obtener la lista de columnas en una tabla junto con los tipos de datos, NO NULL y restricciones CLAVE PRIMARIA

tipos de restricciones en base de datos

Question

Necesito escribir una consulta en el servidor SQL para obtener la lista de columnas en una tabla en particular, sus tipos de datos asociados y su longitud, y si no son nulas. He logrado hacer esto mucho. Pero ahora también necesito entrar en la misma tabla en una columna: VERDADERO si es una clave principal. Cómo hago esto ?

Así es como debe ser la salida:

Columns_name----Data type----Length----isnull----Pk

¡por favor, ayúdame!

Answer 1

Ampliando la respuesta de Alex, puedes hacer esto para obtener la restricción PK

Select C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION, C.IS_NULLABLE, TC.CONSTRAINT_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
On TC.TABLE_SCHEMA = C.TABLE_SCHEMA
And TC.TABLE_NAME = C.TABLE_NAME
And TC.CONSTRAINT_TYPE = ''PRIMARY KEY''
Where C.TABLE_NAME = ''Table''

Debo haber pasado por alto que desea que un indicador determine si la columna dada fue parte de la PK en lugar del nombre de la restricción PK. Para eso usarías:

Select C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
, C.NUMERIC_PRECISION, C.NUMERIC_SCALE
, C.IS_NULLABLE
, Case When Z.CONSTRAINT_NAME Is Null Then 0 Else 1 End As IsPartOfPrimaryKey
From INFORMATION_SCHEMA.COLUMNS As C
Outer Apply (
Select CCU.CONSTRAINT_NAME
From INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As CCU
On CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
Where TC.TABLE_SCHEMA = C.TABLE_SCHEMA
And TC.TABLE_NAME = C.TABLE_NAME
And TC.CONSTRAINT_TYPE = ''PRIMARY KEY''
And CCU.COLUMN_NAME = C.COLUMN_NAME
) As Z
Where C.TABLE_NAME = ''Table''

Answer 2

El procedimiento almacenado sp_columns devuelve información detallada de la tabla.

exec sp_columns MyTable

Answer 3

En SQL 2012 puedes usar:

EXEC sp_describe_first_result_set N''SELECT * FROM [TableName]''

Esto le dará los nombres de columna junto con sus propiedades.

Answer 4

Encuentre el resultado de la combinación para Datatype y Length y es anulable en forma de "NULL" y "Not null". Use la siguiente consulta.

SELECT c.name AS ''Column Name'',
t.name + ''('' + cast(c.max_length as varchar(50)) + '')'' As ''DataType'',
case
WHEN c.is_nullable = 0 then ''null'' else ''not null''
END AS ''Constraint''
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = Object_id(''TableName'')

Encontrará el resultado como se muestra a continuación.

Gracias.

Answer 5

Lanzando otra respuesta al ring, esto te dará esas columnas y más:

SELECT col.TABLE_CATALOG AS [Database]
, col.TABLE_SCHEMA AS Owner
, col.TABLE_NAME AS TableName
, col.COLUMN_NAME AS ColumnName
, col.ORDINAL_POSITION AS OrdinalPosition
, col.COLUMN_DEFAULT AS DefaultSetting
, col.DATA_TYPE AS DataType
, col.CHARACTER_MAXIMUM_LENGTH AS MaxLength
, col.DATETIME_PRECISION AS DatePrecision
, CAST(CASE col.IS_NULLABLE
WHEN ''NO'' THEN 0
ELSE 1
END AS bit)AS IsNullable
, COLUMNPROPERTY(OBJECT_ID(''['' + col.TABLE_SCHEMA + ''].['' + col.TABLE_NAME + '']''), col.COLUMN_NAME, ''IsIdentity'')AS IsIdentity
, COLUMNPROPERTY(OBJECT_ID(''['' + col.TABLE_SCHEMA + ''].['' + col.TABLE_NAME + '']''), col.COLUMN_NAME, ''IsComputed'')AS IsComputed
, CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey
FROM INFORMATION_SCHEMA.COLUMNS AS col
LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA
, o.name AS TABLE_NAME
, c.name AS COLUMN_NAME
, i.is_primary_key
FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.objects AS o ON i.object_id = o.object_id
LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1)AS pk ON col.TABLE_NAME = pk.TABLE_NAME
AND col.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND col.COLUMN_NAME = pk.COLUMN_NAME
WHERE col.TABLE_NAME = ''YourTableName''
AND col.TABLE_SCHEMA = ''dbo''
ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;

Answer 6

Para asegurarse de obtener la longitud correcta, debe considerar los tipos Unicode como un caso especial. Ver código a continuación.

Para obtener más información, consulte: https://msdn.microsoft.com/en-us/library/ms176106.aspx

SELECT
c.name ''Column Name'',
t.name,
t.name +
CASE WHEN t.name IN (''char'', ''varchar'',''nchar'',''nvarchar'') THEN ''(''+
CASE WHEN c.max_length=-1 THEN ''MAX''
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN (''nchar'',''nvarchar'')
THEN c.max_length/2 ELSE c.max_length END )
END +'')''
WHEN t.name IN (''decimal'',''numeric'')
THEN ''(''+ CONVERT(VARCHAR(4),c.precision)+'',''
+ CONVERT(VARCHAR(4),c.Scale)+'')''
ELSE '''' END
as "DDL name",
c.max_length ''Max Length in Bytes'',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) ''Primary Key''
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(''YourTableName'')

Answer 7

Para evitar filas duplicadas para algunas columnas, use user_type_id en lugar de system_type_id.

SELECT
c.name ''Column Name'',
t.Name ''Data type'',
c.max_length ''Max Length'',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) ''Primary Key''
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(''YourTableName'')

Simplemente reemplace YourTableName con su nombre de tabla real: funciona para SQL Server 2005 y superior.

Answer 8

Podrías usar la consulta:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=''TableName''

para obtener todos los metadatos que necesita, excepto la información de Pk.

Answer 9

Prueba esto:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = ''tablename'' and COLUMN_NAME = ''columnname''

Answer 10

wite el nombre de la tabla en el editor de consultas, seleccione el nombre y presione Alt + F1 y traerá toda la información de la tabla.

Answer 11

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = ''Table'')
BEGIN
SELECT COLS.COLUMN_NAME, COLS.DATA_TYPE, COLS.CHARACTER_MAXIMUM_LENGTH,
(SELECT ''Yes'' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON COLS.TABLE_NAME = TC.TABLE_NAME
AND TC.CONSTRAINT_TYPE = ''PRIMARY KEY''
AND KCU.TABLE_NAME = TC.TABLE_NAME
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.COLUMN_NAME = COLS.COLUMN_NAME) AS KeyX
FROM INFORMATION_SCHEMA.COLUMNS COLS WHERE TABLE_NAME = ''Table'' ORDER BY KeyX DESC, COLUMN_NAME
END

Answer 12

SELECT
T.NAME AS [TABLE NAME]
,C.NAME AS [COLUMN NAME]
,P.NAME AS [DATA TYPE]
,P.MAX_LENGTH AS [Max_SIZE]
,C.[max_length] AS [ActualSizeUsed]
,CAST(P.PRECISION AS VARCHAR) +''/''+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID = P.SYSTEM_TYPE_ID
AND C.[user_type_id] = P.[user_type_id]
WHERE T.TYPE_DESC=''USER_TABLE''
AND T.name = ''InventoryStatus''
ORDER BY 2

Answer 13

SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = ''<name_of_table_or_view>''

Ejecute SELECT * en la declaración anterior para ver qué información_schema.columns devuelve.

Esta pregunta ha sido respondida previamente - https://.com/a/11268456/6169225

Answer 14

select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID(''YourTableName1'')
and
t.name like ''%YourSearchDataType%''
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID(''YourTableName2'')
and
t.name like ''%YourSearchDataType%'')
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID(''YourTableName3'')
and
t.name like ''%YourSearchDataType%'')
order by tbl.name

Para buscar qué columna está en qué tabla en función de su tipo de datos de búsqueda para tres tablas diferentes en una base de datos. Esta consulta es expandible a ''n'' tablas.