Click here to Skip to main content
Click here to Skip to main content

Tagged as

List All Tables and Columns in a Database

, 20 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AspDotNetDev
Web Developer
United States United States
  • Managing Your JavaScript Library in ASP.NET (if you work with ASP.net and you don't read that, you are dead to me).
  • Graduated summa cum laude with a BS in Computer Science.
  • Wrote some articles and some tips.
  • DDR ("New high score? What does that mean? Did I break it?"), ping pong, and volleyball enthusiast.
  • Software I have donated to (you should too):

Comments and Discussions

 
GeneralMy vote of 5 PinmemberGregoryW22-Jul-13 0:03 
QuestionAdditional info PinmemberMember 998347719-Jul-13 21:55 
GeneralRe: Additional info PinprotectorAspDotNetDev20-Jul-13 7:54 
GeneralAdded to Tip PinprotectorAspDotNetDev20-Jul-13 7:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 20 Jul 2013
Article Copyright 2013 by AspDotNetDev
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid