One way to familiarize one’s self with a very non-production database is to get a list of tables and record counts for each.
declare @tbl nvarchar(400); -- internally generated, table name declare @sch nvarchar(400); -- internally generated, schema name declare @cnt int; -- internally generated, count of records declare @exc nvarchar(4000); -- variable to contain dynamic sql declare @fetch_status int; -- gives loop status declare FindAllTables cursor forward_only for select table_schema,table_name from information_schema.tables where table_type = 'BASE TABLE'; open FindAllTables; set @fetch_status = 0 while @fetch_status = 0 begin fetch next from FindAllTables into @sch, @tbl set @fetch_status = @@fetch_status; if coalesce(@fetch_status,1) = 0 begin set @exc = N'declare @cnt int;select @cnt = count(*) from [' + @sch + '].[' + @tbl + ']; print ''table ' + @sch + '.' + @tbl + ': ''' + + N' + cast(@cnt as nvarchar(100));'; --print @exc; exec (@exc); end; end; close FindAllTables; deallocate FindAllTables;
This will list out all the tables in the database currently selected. Feel free to insert the USE command at the top of this script to programmatically define the working database. Remember, this script should never be ran in a live production database.
You can also change the query for the cursor variable “FindAllTables” to find only the tables you wish to include in your query. You can also conditionally execute the dynamic sql variable “@exc”. Remember, you must have select privileges on all the tables you wish to iterate.
At eimagine, developers get to work on all kinds of technologies and have lots of fun together in a friendly and collaborative workplace. SQL Server is just one of many disciplines to learn and master. I hope you find this post useful.