Queries to retrieve table and column definitions in SQL Server

T-SQL script to dynamically retrieve column information in SQL Server. Replace 'YourTableName' below with the table name of interest (whose definition you wish to retrieve).

SELECT OBJECT_name(c.object_id) AS Table_Name, 
      c.name AS Column_Name,
      t.name AS Column_Data_Type,
      c.max_length AS Column_Data_Type_Length
FROM sys.columns AS c, sys.types AS t 
WHERE c.user_type_id=t.user_type_id
AND OBJECT_name(c.object_id) = 'YourTableName'
ORDER BY c.column_id

.... and in the event that you wish to retrieve a list of all tables in SQL Server;

–---- All tables
EXEC sp_tables 

–---- Alternatively, if you need to filter further
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘IsMsShipped’) = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME