Ok, no bling in this post, just a down-and-dirty method to use the system catalog views to return all columns and associated metadata for the current database.
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], I.[name] AS [index_name],
AC.[name] AS [column_name], I.[type_desc], I.[is_unique],
I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key],
I.[is_unique_constraint], I.[fill_factor], I.[is_padded], I.[is_disabled],
I.[is_hypothetical], I.[allow_row_locks], I.[allow_page_locks],
IC.[is_descending_key], IC.[is_included_column]
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> ‘HEAP’
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]
Simon, thank you for the correction. I’m not quite sure how I overlooked that, as I know better. The script has been corrected and the moose responsible for editing the code (me) has been sacked.
Tim, it might be worth mentioning that this only works on SP2 and forward for SQL Server 2005.
You also need to add a join to the index ID between sys.[indexes] and sys.[index_columns]
Currently it’s just between object ID’s
Simon, thank you for the correction. I’m not quite sure how I overlooked that, as I know better. The script has been corrected and the moose responsible for editing the code (me) has been sacked.