If you want to look for a single table use
EXEC SP_HELP <Table_Name>
OR
SELECT
[DB Name] = isc.TABLE_CATALOG
,[Table Schema] = isc.TABLE_SCHEMA
,[Table Name] = isc.TABLE_NAME
,[Column Name] = isc.COLUMN_NAME
,[Data Type] = DATA_TYPE
,[Data Type Size] = [CHARACTER_MAXIMUM_LENGTH]
,[Default Value] = COLUMN_DEFAULT
,[Constaint] = isccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS isc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE isccu
ON isc.TABLE_NAME = isccu.TABLE_NAME
WHERE isc.TABLE_NAME = '<Table Name>'
For All tables, you can use
SELECT
[DB Name] = isc.TABLE_CATALOG
,[Table Schema] = isc.TABLE_SCHEMA
,[Table Name] = isc.TABLE_NAME
,[Column Name] = isc.COLUMN_NAME
,[Data Type] = DATA_TYPE
,[Data Type Size] = [CHARACTER_MAXIMUM_LENGTH]
,[Default Value] = COLUMN_DEFAULT
,[Constaint] = isccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS isc
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE isccu
ON isc.TABLE_NAME = isccu.TABLE_NAME
WHERE isc.TABLE_NAME IN (SELECT NAME FROM SYS.TABLES WHERE TYPE='U')
Best Regards,
Niladri Biswas
mareesnectar-12158, if this helps please login to Mark As Answer. | Alert Moderator