List All Tables and Columns in a Database






4.71/5 (4 votes)
This query will show you all tables and columns in a database, and it can be pasted into Excel for easy manipulation.
This is a query I created to show all the tables in a database and all of the columns on those tables (the columns also show their type, not including things like max length). If you run this in SSMS and output the results to text rather than to a grid, you can just paste it into Excel. You'll get one table per row, and one field per column when you paste into Excel.
-- Optional (may already be set by SQL connection.
USE NameOfYourDatabase
GO
-- Parameters.
DECLARE @DatabaseName AS varchar(max)
SET @DatabaseName = 'NameOfYourDatabase'
-- Variables.
DECLARE @TabChar AS varchar(1)
DECLARE @Name AS varchar(256)
DECLARE @Column AS varchar(256)
DECLARE @Type AS varchar(256)
DECLARE @Columns AS varchar(max)
DECLARE @Tables AS table(info varchar(max))
-- Cursor to query table names.
DECLARE TableCursor CURSOR FOR
SELECT
name
FROM sys.Tables
WHERE
type_desc = 'USER_TABLE'
ORDER BY
name ASC
-- Loop through each table name.
OPEN TableCursor
GOTO FetchNextTable
WHILE @@FETCH_STATUS = 0
BEGIN
-- Variables.
SET @TabChar = ''
SET @Columns = ''
-- Cursor to query column info.
DECLARE ColumnCursor CURSOR FOR
SELECT
Column_Name, Data_Type
FROM information_schema.columns
WHERE
Table_Catalog = @DatabaseName
AND Table_Name = @Name
ORDER BY
Ordinal_Position ASC
-- Loop through each column.
OPEN ColumnCursor
GOTO FetchNextColumn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Columns = @Columns + @TabChar + @Column + ' AS ' + @Type
SET @TabChar = CHAR(9)
FetchNextColumn:
FETCH NEXT FROM ColumnCursor INTO @Column, @Type
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
-- Store the table name and column information.
INSERT INTO @Tables(info) VALUES (@Name + @TabChar + @Columns)
-- Next table.
FetchNextTable:
FETCH NEXT FROM TableCursor INTO @Name
END
CLOSE TableCursor
DEALLOCATE TableCursor
-- Show all table information.
SELECT * FROM @Tables
Notice that there is a variable near the top that you'll need to change to match the name of your database. Also, if you include the command (at the top) to specify the database, you'll have to change that to your database name too.