Skip to main content

Posts

Showing posts from October, 2010

SQL Server Scripts - Part 1

This post will start a series of useful MS SQL Server scripts.

Here's the one to display all tables and related columns that were created in the certain database:

/* Begin */
SELECT 

a.[TABLE_NAME],
c.COLUMN_NAME

FROM
(
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
) a

INNER JOIN

(
SELECT
s.[name] as COLUMN_NAME,
tt.name as TABLE_NAME FROM syscolumns s INNER JOIN (SELECT * FROM sysobjects WHERE type = 'U') tt ON tt.id = s.id
) c

ON c.TABLE_NAME = a.TABLE_NAME
ORDER BY a.TABLE_NAME
/* End */

The following script will display just table names:

/* Begin */
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
/* End */

Enjoy! :)