Post a Comment On: SqlInk

Today, I had to prepare a document containing all the tables and their column data types. At first, I was going to do it by hand, but then thought I could use the system tables to get it working.

The following is the T-SQL that I wrote to automate the process for me. I just copied the output and pasted it into MS Word.

USE PaHub
GO

DECLARE c1 CURSOR FOR SELECT [name], [object_id] FROM sys.all_objects WHERE type_desc='USER_TABLE'
DECLARE @tabname VARCHAR(200), @tabid VARCHAR(200)
OPEN c1

FETCH NEXT FROM c1 INTO @tabname, @tabid
WHILE (@@FETCH_STATUS=0)
BEGIN
--PRINT @tabname + ' ' + @tabid
PRINT 'Table: ' + @tabname
DECLARE c2 CURSOR FOR
SELECT [name], [system_type_id], CASE [system_type_id]
WHEN 167 THEN '(' + CAST( max_length AS VARCHAR(10)) + ')'
ELSE ''
END
AS tempsizetxt
FROM sys.all_columns WHERE object_id=@tabid
BEGIN
OPEN c2
DECLARE @colname VARCHAR(200), @coltype INT, @typename VARCHAR(200), @typesizetxt VARCHAR(200)
FETCH NEXT FROM c2 INTO @colname, @coltype, @typesizetxt
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @typename=UPPER([name]) FROM sys.types WHERE system_type_id=@coltype
PRINT ' ' + @colname + ' ' + @typename + @typesizetxt
FETCH NEXT FROM c2 INTO @colname, @coltype, @typesizetxt
END
CLOSE c2
DEALLOCATE c2
END
PRINT '---'
FETCH NEXT FROM c1 INTO @tabname, @tabid
END

CLOSE c1
DEALLOCATE c1