I get column names from schema-qualified table metadata. Catalog views help when I also need SQL Server-specific details.
Run this in the database containing the table and set both schema and table name. The original scripts left their filters commented out, so they listed more than the requested table.
DECLARE @Schema sysname = N'HumanResources', @Table sysname = N'Employee';
SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table
ORDER BY ORDINAL_POSITION;
SELECT s.name AS SchemaName, o.name AS TableName, c.name AS ColumnName,
t.name AS DataType, c.max_length AS MaxLengthBytes,
c.precision AS NumericPrecision, c.scale AS NumericScale
FROM sys.tables AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.columns AS c ON c.object_id = o.object_id
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE s.name = @Schema AND o.name = @Table
ORDER BY c.column_id;
Use c.max_length for the column, rather than the type’s default length. It reports bytes, with -1 for max types. Precision and scale describe separate numeric attributes. Permissions affect visibility, so no rows can mean invisible as well as absent.
Reference: sys.columns.
Original video
A column length is not always a character count, it is metadata whose units depend on the data type.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
17 Comments. Leave new
I just select the name of the table and press Alt + F1, which gives all the columns, indexes etc.
I just select the table name and press Alt + F1, which lists out the table name, indexes, etc.
Are you daft? this is for when you need to do it programmatically
How to get columns for temporary table?
Refer this post https://alveenajoyce.wordpress.com/2019/11/01/methods-to-find-table-structure-in-sql/
Is it at all possible to find when a Column was added to an Existing Table (datetime of the new column added and not the Table in whole).
I am using sp_columns as it is shorter to write :)
SELECT *
FROM sys.dm_exec_describe_first_result_set( ‘SELECT * FROM schema.table, NULL, 1 )
If the goal is just to get the column names, a quick way is just to select and return an empty set.
select * from dbo.tablename where 0=1
However, if you want or need additional details, then the examples given would be a better methodology.
select * from sys.columns a inner join sys.tables b on a.object_id=b.object_id where b.name =’UserTableName’
select SC.* from sys.columns SC join sys.tables ST on SC.Object_ID = ST.Object_ID
where ST.Name =
select SC.* from sys.columns SC join sys.tables ST on SC.Object_ID = ST.Object_ID
where ST.Name = ‘table_name’
Here are two other simple methods to do the same
https://madhivanan.wordpress.com/2017/07/03/how-to-get-column-names-from-a-specific-table/
Is there a way to do this for all views?
same script including indexes and keys information, is there any way to add in the same script, Please help
Hey Pinal,
I really enjoy reading through your Tips and Tricks. Really neat.
By the way, I believe the Method 2 has a slight error. The 5th column “Length_Size” is marked as “t.max_length AS Length_Size”. I believe you meant that to be “c.max_length AS Length_Size”.
Refer this post to know different methods to know structure of the table https://alveenajoyce.wordpress.com/2019/11/01/methods-to-find-table-structure-in-sql/