SQLTeam.com | Weblogs | Forums

SQL SERVER EXTRACT METADATA & 100 rows for each table

Hi,
I am new to SQL-Server and just need help:
I need to extract the table name, column name, data_type, nullable and unique 100 rows for all the tables under a database.

Off Top of my head .. Needs to be tweaked

SELECT
table name, column name, data_type, nullable
FROM
INFORMATION_SCHEMA.columns

EXEC sp_MSforeachtable 'SELECT ''?'' TableName, Top 100 distinct * FROM ?'

Why are you trying to extract 100 rows of each table? I ask because this sounds like you're trying to extract a small set of data to copy to a development server. Simply doing so will probably fail because of the relationships that are formed by DRI (Declared Referential Integrity). If you don't copy the FKs (Foreign keys), the data could prove useless for such a task and if you do copy them (they're part of the meta data you're talking about), then just copying and inserting 100 rows from each table will fail. Either way is a failure.

To make a much smaller subset of the data in a database auto-magically is incredibly difficult, especially since there can be more than 1 chain of FKs that enforce the quality of the data.

1 Like

I just need to analyze the data,get some view of what data is being stored in each table

Where do you want to extract it to

Excel and can also copy from the result window. Just need the query to achieve this

This will give result in multiple result windows, I need all data in one windows for me to easily export to excel

How many tables you got?

Select count(1) from sys.tables

Max will be 1000 table and each table on average 10 rows

hi

use this query .. remove the last union all ...
if there are too many tables and the script becomes
too large

then you will have to Loop through all the tables
and build the result set

select 
   'select top 100 * , '''+name+'''  from '+ name +' union all'
from 
sys.tables

Sorry,this didn't worked.

DB0
TB1
C1
C2
TB2
C3
C4

I need output as :

Table Name Col Name Data
TB1 C1 Select top 10 C1 from TB1
TB1 C2 Select top 10 C2 from TB1
TB2 C3 Select top 10 C1 from TB2
TB2 C4 Select top 10 C1 from TB2

This is what is needed

You are not the only guy

Or first time you have this requirement

Many many people
many many times

This execution gives result for each iteration in a new result pane,I need to have it in one result pane aka window

Please help

You can do it in ssms

Results to text mode

And copy paste

select t.name as 'Table Name', c.name 'Col Name', 
'select ' + c.name + ' from ' + t.name as Data
  from sys.tables t
  join sys.columns c on t.object_id = c.object_id

Thanks but it not executing the query in the data col
Table Name Col Name Data
1 Pilots Birth_date select Birth_date from Pilots
2 Pilots First_name select First_name from Pilots
3 Pilots ID select ID from Pilots
4 Pilots Last_name select Last_name from Pilots

Could you check once please

it is not going to work because you could have many different type of data types on your columns. but try the following.

use sqlteam
go

declare @id int ;

create table #bootleg(id int identity(1,1), 
                      tablename varchar(max), 
					  columnname varchar(max), 
					  dada nvarchar(max))

DECLARE @tablename VARCHAR(MAX), 
        @columnname VARCHAR(MAX),
		@query  VARCHAR(MAX)

DECLARE cursor_zoro CURSOR
FOR 
select t.name as 'Table Name', c.name 'Col Name', 
       'select top 100 ' + c.name + ' from ' + t.name as query
  from sys.tables t
  join sys.columns c 
    on t.object_id = c.object_id;

OPEN cursor_zoro;

FETCH NEXT FROM cursor_zoro INTO 
    @tablename, 
    @columnname,
	@query;

WHILE @@FETCH_STATUS = 0
    BEGIN
		insert into #bootleg(dada)
		exec(@query)

		set @id = @@IDENTITY;
		
		update #bootleg
		  set tablename = @tablename,
		      columnname = @columnname
		  where id = @id

        FETCH NEXT FROM cursor_zoro INTO 
            @tablename, 
            @columnname,
			@query;
    END;

CLOSE cursor_zoro;

DEALLOCATE cursor_zoro;

select * From #bootleg

drop table #bootleg

Thanks this is working but still there are issues.

It is putting the table name and col name only for the last value of top 100. For rest it is putting null,see attached screenshot