April 4, 2019 at 3:48 pm
This query retrieves column names and their data type along with the tables they're in.
SELECT
st.name 'Table Name',
c.name 'Column Name',
t.name 'Data Type'
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
LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id
I was trying to select top 1 column value from the table to have a glimpse of the data, based on the output
(i.e. equivalently,
SELECT c.name FROM st.Name
I have been trying to use a dynamic sql, but as it should put the table name in a single quotation as a string, it couldn't work; when I try to avoid that, it just displays the declared variable.
Any idea is much appreciated. Thanks!
April 7, 2019 at 2:05 pm
SELECT (TOP 1)
st.name ‘Table Name’,
c.name ‘Column Name’,
t.name ‘Data Type’
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
LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id
April 8, 2019 at 4:43 pm
You don't want index_columns and indexes in the query as it exists, because it will just generate duplicate rows for no reason.
SELECT TOP (1)
st.name [Table Name],
c.name [Column Name],
t.name [Data Type]
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.tables st ON st.object_id = i.object_id
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply