SQL Server 2008 Standard, get all tables with all columns and possible data within the colums

Question

I am currently working on a big and highly normalized database with 3k+ tables. Besides a small data dictionary there is no documentation which will help me find the right data (it is from a takeover).

Is there any way to create a SQL script which will give me all columns of all tables with all the contents? The result should be the same as grouping every single column alone in every table.

I hope you got me. Sorry my English is a little rusty at the moment.

If there is no way within SQL Server 2008 (Standard), I will create me a small helper in Java or C#.

Thanks in advance! bg Patrick

Answer 1

Try it like this. This statement will generate one statement like

SELECT colABC FROM SomeTable GROUP BY colABC

for every single column and return the whole lot in one single XML.

But be aware that this might get huge and could take hours. For my test I excluded some data types which tend to hold ungrouped data

You will - for sure - get the exception, that the generated statement has to many columns. Reduce the input to decent tabel's names or split the query in parts.

For testing purpose there is a TOP 100 which you'd have to remove and replace by your own logic:

DECLARE @Commands TABLE(ID INT IDENTITY,cmd NVARCHAR(MAX));

WITH AllColumns AS
(
    SELECT t.TABLE_CATALOG,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE
    FROM INFORMATION_SCHEMA.TABLES AS t 
    INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
    WHERE t.TABLE_TYPE='BASE TABLE'
)
INSERT INTO @Commands(cmd)
SELECT TOP 100 '(SELECT ' + QUOTENAME(COLUMN_NAME) + ' AS [*]'
     + ' FROM '  + QUOTENAME(TABLE_CATALOG) + '.' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) 
     + ' GROUP BY ' + QUOTENAME(COLUMN_NAME)
     + ' FOR XML PATH(''x''),TYPE) AS ' + QUOTENAME(TABLE_CATALOG + '_' + TABLE_SCHEMA + '_' + TABLE_NAME + '_' + COLUMN_NAME)   
FROM AllColumns
WHERE DATA_TYPE NOT IN('image','text','uniqueidentifier','datetime','xml') AND DATA_TYPE NOT LIKE '%binary%' ;

DECLARE @finalCommand NVARCHAR(MAX)=
(
    SELECT 'SELECT '
          + STUFF(
            (
                SELECT ',' + cmd
                FROM @Commands
                ORDER BY ID
                FOR XML PATH('')
            ),1,1,''
            )
          + ' FOR XML PATH(''AllColumnsDistinct'')'
);

EXEC( @finalCommand);