SQL SERVER EXTRACT METADATA & 100 rows for each table

21

oops. here you go. I have excluded xml and timestamp columns because the target data column is nvarchar(max). You will have to sort that out.

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 tablename, 
       c.name columnname, 
       '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
	join sys.types tt on tt.user_type_id = c.user_type_id
	where tt.name not in ('timestamp', 'xml')
create table #data(dada nvarchar(max))

OPEN cursor_zoro;

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

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

		;with src
		as
		(
			select dada, @tablename as tablename, @columnname as columnname
			 from #data
		)
		insert into #bootleg(tablename, columnname, dada)
		select tablename, columnname, dada
		  from src

		truncate table #data;

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

CLOSE cursor_zoro;

DEALLOCATE cursor_zoro;

select * From #bootleg

drop table #bootleg
2 Likes
22

Sorry my bad, this is working fine. I was executing online where the "WHERE" conditions was not getting satisfied and hence no table was returned. When I removed the where conditions, it worked.
Once again thanks.

Any scenario where the data column will fail to hold value for any data type...

23

Probably not but it'll certainly be fun when the top 100 rows are riddled with large LOBs.

I guess still I don't understand why you think doing this is going to be better than manually going through tables in SSMS. There, you can use the "Diagrams" feature to nearly automatically and pictorially see the relationships between tables (if proper DRI has been used) and it takes but a flick of the wrist to see the top 100 or so rows of tables without all this hassle and you weeding through a shedload of results.

Of course, it would be much simpler to read the documentation that folks provide about the databases they build, right? (sorry... bad joke right there).

2 Likes
24

yes, that was an easter egg for you there. dont just copy pasta. understand first before implementing :wink: