List SQL Server Database Tables and Columns

Query for Showing All Tables and Columns in Your Database

Do you need a list of all the tables in your database and their columns?

Use SQL Server Management Studio to connect to your database.   Execute this query, substituting your database name in the USE statement.


USE YOUR_DATABASE_NAME;
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]


Right-click on the query results grid and select "Save Results As..."
Choose the .csv file format so you can open the file in Excel.

---

Visit www.BlueCanyonSoftware.com if you need custom software development for your business.