get no. of columns with no. of rows for each table from synapse lake database

Question

I want to get the list of all tables from synapse lake data base ( this database created after creating the synapse link with data verse - fetching data from D365 FnO tables). I am getting all the tables with no. of columns but I am having hard time to get no. of rows in next column after column count. Again I am running this in LAKE database in Synapse workspace.

Answer 1

create a temporary table name #tt

create table #tt (tablename varchar(100),objectid varchar(20), number_of_rows int)

create a cursor names tcursor as shown below, which will get number of rows of each table and insert into #tt

declare @name as varchar(100)
declare @objectid as varchar(20)
declare @qry as varchar(500)

DECLARE tcursor CURSOR FOR  
select name,object_id from sys.all_objects where type_desc='User_table';
open tcursor ;

FETCH NEXT from tcursor into @name,@objectid;
while @@FETCH_STATUS=0

begin
set @qry='insert into #tt (tablename,objectid,number_of_rows) select '''+@name+'''  tablename,'''+@objectid+''' objectid, count(*) number_of_rows from '+@name

 exec(@qry)


FETCH NEXT FROM tcursor into @name,@objectid;  
END  
  
CLOSE tcursor;  
DEALLOCATE tcursor;  

now execute next statement to get desired output

select col.name,col.NumberOfColumns,ro.number_of_rows from (
select count(c.name) NumberOfColumns,t.object_id objectid,t.name from sys.tables t join sys.columns c on c.object_id=t.object_id group by t.object_id,t.name)col
join #tt ro on ro.objectid=col.objectid