Query to return sample from each column of the DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query to return sample from each column of the DB

Author

Topic

russs
Starting Member

12 Posts

Posted - 2012-07-09 : 04:16:38

Hi all,

I need to write a query that, for a given DB, will show:
- all the tables
- every column for these tables
- the data type
- and 3 examples of every column, the examples should be the first elements of the column when the table is sorted by its Primary Key

So far, I made a query that does the first 3:

 select TABLE_NAME , COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION

from INFORMATION_SCHEMA.COLUMNS

the last one seems really hard, can you please help?

Thank you,
Kol

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 07:39:38

If ordering were not an issue, you could use the undocumented stored proc sp_MSForeachTable to get the data from each table. Then the query would be like this:

EXEC sp_MSforeachtable 'SELECT TOP (3) * FROM ?'

If you want to order it by the primary key you will need to find the column(s) that make up the primary key, account for the fact that some tables may not have a primary key and some may have composite primary key etc.

The general approach to finding the primary key column can be this:

SELECT
	table_name,
	column_name
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
	OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1;

That can then be used in a dynamic SQL statement to do the select.

There may be simpler approaches - but nothing comes to my mind. But there are other minds on this forum who have more skills and in-depth knowledge than I do. Let us see if anyone will offer a simpler approach.

russs
Starting Member

12 Posts

Posted - 2012-07-09 : 08:18:23

The problem I am facing is that I cannot use the output of the first query I am running :

select TABLE_NAME , COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION

from INFORMATION_SCHEMA.COLUMNS

in a second query, I want to use the COLUMN_NAME and TABLE_NAME values to build a new query that does the following:

select COLUMN_NAME from TABLE_NAME

I tried creating a cursor with the first query and then going through it and building a new dynamic SQL query but it's a mess and doesn't work :(


DECLARE @table VARCHAR(50), @column VARCHAR(50), @type VARCHAR(50), @precision VARCHAR(50), @pk VARCHAR(50)

DECLARE result CURSOR FOR
select I.TABLE_NAME , I.COLUMN_NAME, I.DATA_TYPE, I.NUMERIC_PRECISION, C.CONSTRAINT_NAME

from INFORMATION_SCHEMA.COLUMNS as I, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as C

where C.CONSTRAINT_NAME like 'PK_%' and I.TABLE_NAME = C.TABLE_NAME

OPEN result
FETCH result INTO @table, @column, @type, @precision, @pk

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE ('SELECT ' + @column + ' FROM ' +@table)
FETCH result INTO @table, @column, @type, @precision, @pk

End

CLOSE result
DEALLOCATE result

any advice please ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources