Thursday, April 4, 2013

SQL - List Database Tables, Fields, and Other Information

There are a few SQL scripts around for listing table information. This is the one that I use. Just replace "YourDatabaseName" with the name of the database.

USE [YourDatabaseName];
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
T.[name] AS [table_name], AC.[name] AS [column_name],  
TY.[name] AS system_data_type, AC.[max_length], 
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded] 
FROM sys.[tables] AS T  
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]  
WHERE T.[is_ms_shipped] = 0 
ORDER BY T.[name], AC.[column_id]

No comments:

Post a Comment

Thank you for commenting!