declare @table varchar(50)
set @table = 'TableName'
SELECT
Columna,
[Description] as Descripcion,
Tipo,
--cname,
Tamaño
FROM
(
SELECT
so.name AS Tabla,
sc.name AS Columna,
st.name AS Tipo,
st.collation_name as cname,
sc.max_length AS Tamaño,
SC.column_id CID
FROM sys.objects so
INNER JOIN sys.columns sc ON so.object_id = sc.object_id
INNER JOIN sys.types st ON
st.system_type_id = sc.system_type_id
AND st.name != 'sysname'
WHERE so.type = 'U' and so.name = @table
--ORDER BY so.name,sc.name
)DICCIONARIO
INNER JOIN
(
SELECT
[TableName] = i_s.TABLE_NAME,
[ColumnName] = i_s.COLUMN_NAME,
[Description] = s.value
FROM INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN sys.extended_properties s ON
s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.minor_id = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
AND i_s.TABLE_NAME = @table
-- ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITION
)diccionario2 ON
Tabla = diccionario2.[TableName] AND Columna = [ColumnName]
ORDER BY CID
Comentarios sobre la versión: Versión 1 (2)
Que tipo de problema tiene?!!!
saludos
declare @table varchar(50)
set @table = 'Articulos'
SELECT
Columna,
[Description] as Descripcion,
Tipo,
--cname,
Tamaño
FROM
(
SELECT
so.name AS Tabla,
sc.name AS Columna,
st.name AS Tipo,
st.collation_name as cname,
sc.max_length AS Tamaño,
SC.column_id CID
FROM sys.objects so
INNER JOIN sys.columns sc ON so.object_id = sc.object_id
INNER JOIN sys.types st ON
st.system_type_id = sc.system_type_id
AND st.name != 'sysname'
WHERE so.type = 'U' and so.name = @table
--ORDER BY so.name,sc.name
)DICCIONARIO
INNER JOIN
(
SELECT
[TableName] = i_s.TABLE_NAME,
[ColumnName] = i_s.COLUMN_NAME,
[Description] = s.value
FROM INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN sys.extended_properties s ON
s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.minor_id = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
AND i_s.TABLE_NAME = @table
-- ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITION
)diccionario2 ON
Tabla = diccionario2.[TableName] AND Columna = [ColumnName]
ORDER BY CID