SQL Clever

Hi guys!

Here comes a simple script to show a list of all columns of each table in a database.
It could be useful to help the know about database model.

DECLARE @obj_id INT
DECLARE @obj_name VARCHAR(300)
DECLARE @columns AS VARCHAR(MAX)

IF Object_id(‘tempdb..#tmp_table’) > 0
BEGIN
DROP TABLE #tmp_table
END

CREATE TABLE #tmp_table
(
table_name VARCHAR(300),
columns VARCHAR(MAX)
)

SET @obj_id=100

WHILE EXISTS (SELECT TOP 1 1
FROM sys.tables
WHERE object_id > @obj_id)
BEGIN
SELECT TOP 1 @obj_id = object_id,
@obj_name = Schema_name(schema_id) + ‘.’ + name
FROM sys.tables
WHERE object_id > @obj_id

SET @columns=”

SELECT @columns = @columns + name + ‘, ‘
FROM sys.columns
WHERE object_id = @obj_id
ORDER BY name

INSERT INTO #tmp_table
SELECT @obj_name,
Substring(@columns, 0, Len(@columns) – 1)
END

SELECT table_name, columns
FROM #tmp_table
ORDER BY table_name