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

[getting all tables and columns](https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database) — user5226582, Oct 07 '16 at 07:53
You might look at this: http://stackoverflow.com/a/39518959/5089204 This solution will return one big XML with the entire database. It was easy to add metadata lile a column's data type. If your db is very big, you might add a `TOP 5` to the created statements. This will return just some rows. Enough to get the structure... — Shnugo, Oct 07 '16 at 08:18
Hi, if you are interested in the answer linked above, I just edited it to avoid problems with binary columns... — Shnugo, Oct 07 '16 at 08:40
@user5226582, I think, that your link does not help... I got it the same way as you, but - reading again - I think that the OP wants to get an overview for existing values. — Shnugo, Oct 07 '16 at 09:09

score 2 · Accepted Answer · answered Oct 07 '16 at 09:08

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);

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

1 Answers1