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.tablesSorry,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
