export table and column names ???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

export table and column names ????

Author

Topic

smith2487
Starting Member

9 Posts

Posted - 2012-02-29 : 11:08:20

Hello Everyone,
I want to export the names of tables and the column names within the tables of a SQL 2008 R2 database to be viewed in Excel or similar. I don't want any data exported, just table and column names. A bonus would be to only export the names of tables that are populated. Does anyone know how to do this?
Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:14:36

SELECT * FROM INFORMATION_SCHEMA.Columns

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

smith2487
Starting Member

9 Posts

Posted - 2012-02-29 : 13:29:40

Just tried your suggestion.
Works beautifully.
Do you know if there's a way to only have populated tables returned in the result?
Thanks Brett.
U da Man!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:03:58

[code]

SET NOCOUNT ON
GO

CREATE TABLE #myTable99(TABLE_NAME varchar(256), TBL_ROWS int)
GO

DECLARE myCursor99 CURSOR
FOR
SELECT 'SELECT ' + '''' + TABLE_NAME + '''' + ', COUNT(*) AS TBL_ROWS FROM ' + TABLE_NAME AS SQL
FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @SQL varchar(8000)

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #myTable99(TABLE_NAME, TBL_ROWS)
EXEC(@SQL)
FETCH NEXT FROM myCursor99 INTO @SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT t.TBL_ROWS, c.*
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN #myTable99 t
ON c.TABLE_NAME = t.TABLE_NAME
WHERE TBL_ROWS <> 0
GO

DROP TABLE #myTable99
SET NOCOUNT OFF
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-29 : 14:34:09

This should be faster as it doesn't access the tables to count rows:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE EXISTS(
	SELECT * FROM sys.partitions
	WHERE object_id=object_id(c.TABLE_NAME)
	AND rows>0)

This won't work with SQL Server 2000 or earlier versions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources