SQL research to return all database tables and their records
by Mountain Computers Inc, Publication Date: Saturday, July 13, 2024
View Count: 249, Keywords: SQL, SSMS, Database, Research, Analysis, Hashtags: #SQL #SSMS #Database #Research #Analysis
I had the task to explore an extremely large database that was legacy in nature. The size was small, ~200GB yet very complicated and very long in the tooth, if you know what I mean. It was a very chatty and verbose database with lots of complexity. The problem was the client/server database application GUI was no longer going to be accessible because of license expiration, so I realized, we need a way to quickly explore this database from SSMS or Data Studio for whatever we might need. Here was my 2 hours of work to come up with something worthy of helping.
There are two SQL scripts below;
1. SCRIPT #1 for AdventureWorks2022 style of table ownership, and then
2. SCRIPT #2 for most likely typical database designs where table ownership is dbo. predominantly.
👍😁😏
Good Luck. more to come...
-- begin SCRIPT #1
-- creator: andrew flagg
-- date: july 13, 2024
-- purpose:
-- use this script to briefly search and also deep dive search into a large database with hundreds/thousands of tables with huge rows and columns and records you are looking for
-- once you find the specific table, then Database Diagrams to add your target table and then Add Related tables and Arrange Tables, then your continue analysis
-- run the query in ssms(t-sql) to see the top 5 or 10 returned results per all tables that are not dbo, sys, or schema related
-- when ready to save to txt file, run the following from a cmd prompt using the following cmd example where the sql query file will output the file there as well
-- .\sqlcmd -i SQLQuery07132024.sql -o myresults.txt
-- using AdventureWorks2022 db sample, 88 tables were found, and the myresults.txt filesize was 183MB
-- to analyze your results from ms-dos, use the following example
-- use cmd .\find /n /i "{whatever}" myresults.txt
-- this will narrow you into your target tables and content by line number and case insensitve text
-- open in an text editor capable of large text files > 100MB to get to the line number and find the table name of the targetted text
-- note: varchar vs nchar was considered for UTF-8 yet there were some issues.
-- note: quoted identifier must be ON to avoid error in sql results being displayed in some cases
-- note: do not insert additional GO commands in this script. they are where they need to be. if you do, it might break variable references. just a word of caution
-- warning: do not run this script in ssms(t-sql) using the select * in the cursor fetch begin end. it will lock up your computer.
-- on line 73 and 74 below be sure to consider whatever size you choose, keep the number in "select top (number)" small, like 5, 10, 100.. start small
-- reference line 73 line 74, keep top (##) low and do not use the select * in ssms unless database, tables and returned rows are small < 100k, adventureworks 2022 returns > 800K rows
-- depending on the size of your database, the number of tables and size of selected return rows
-- caveat: its best to use the sqlcmd -i and -o to output the possibility of a huge return of tables and records. your gui could be overwhelmed if you try it otherwise. just a word of caution.
-- change the following to your database
use AdventureWorks2022
SET QUOTED_IDENTIFIER ON
create table #tmpTablesinDB (
T_ROW_ID integer identity,
T_QUALIFIER varchar(100),
T_OWNER varchar(100),
T_NAME varchar(100),
T_TYPE varchar(100),
T_REMARKS varchar(100)
)
GO
insert into #tmpTablesinDB
exec sp_tables
GO
Declare @T_ROW_ID integer
Declare @T_QUALIFIER varchar(255)
Declare @T_OWNER varchar(255)
Declare @T_NAME varchar(255)
Declare @T_TYPE varchar(255)
Declare @T_REMARKS varchar(255)
-- create cursor to walk through the return result of tables in database with the following conditions
declare temp_cursor CURSOR FOR
select * from #tmpTablesinDB where T_OWNER != 'dbo' and T_OWNER != 'information_schema' and T_OWNER != 'sys'
-- open cursor and we are focused on the @T_OWNER and @T_NAME columns
open temp_cursor
fetch next from temp_cursor into @T_ROW_ID, @T_QUALIFIER, @T_OWNER, @T_NAME, @T_TYPE, @T_REMARKS
-- beging cursor
while @@FETCH_STATUS = 0
begin
declare @sql varchar(255)
--swap out comment/uncomment the following two selects for testing.
--why? top 10 rows per table versus all rows per table, select top (10) while running in ssms(t-sql) to view the results and select * doing the sqlcmd -i -o example from cmd prompt
-- reference line 73 line 74, keep top (##) low and do not use the select * in ssms unless database, tables and returned rows are small < 100k, adventureworks 2022 returns > 800K rows
-- select @sql = 'select * from ' + @T_OWNER + '.' + @T_NAME
select @sql = 'select top (5) * from ' + @T_OWNER + '.' + @T_NAME
--give me the table name from the sql query
select @sql as [sql_query]
-- give me the results
EXECUTE(@sql)
fetch next from temp_cursor into @T_ROW_ID, @T_QUALIFIER, @T_OWNER, @T_NAME, @T_TYPE, @T_REMARKS
end
GO
close temp_cursor
deallocate temp_cursor
GO
drop table #tmpTablesinDB
GO
-- end SCRIPT #1
The following is for non-AdventureWorks2022 format of database and table
ownership where as dbo. tables are widely used, and in most cases so is
your production or test database sample.
-- begin SCRIPT #2
-- creator: andrew flagg
-- created date: july 13, 2024
-- modify date: july 14, 2024
-- purpose:
-- use this script to briefly search and also deep dive search into a large database with hundreds/thousands of tables with huge rows and columns and records you are looking for
-- once you find the specific table, then Database Diagrams to add your target table and then Add Related tables and Arrange Tables, then your continue analysis
-- run the query in ssms(t-sql) to see the top 5 or 10 returned results per all tables that are not dbo, sys, or schema related
-- when ready to save to txt file, run the following from a cmd prompt using the following cmd example where the sql query file will output the file there as well
-- .\sqlcmd -i SQLQuery07132024.sql -o myresults.txt
-- using AdventureWorks2022 db sample, 88 tables were found, and the myresults.txt filesize was 183MB
-- to analyze your results from ms-dos, use the following example
-- use cmd .\find /n /i "{whatever}" myresults.txt
-- this will narrow you into your target tables and content by line number and case insensitve text
-- open in an text editor capable of large text files > 100MB to get to the line number and find the table name of the targetted text
-- note: varchar vs nchar was considered for UTF-8 yet there were some issues.
-- note: quoted identifier must be ON to avoid error in sql results being displayed in some cases
-- note: do not insert additional GO commands in this script. they are where they need to be. if you do, it might break variable references. just a word of caution
-- warning: do not run this script in ssms(t-sql) using the select * in the cursor fetch begin end. it will lock up your computer.
-- on line 73 and 74 below be sure to consider whatever size you choose, keep the number in "select top (number)" small, like 5, 10, 100.. start small
-- reference line 73 line 74, keep top (##) low and do not use the select * in ssms unless database, tables and returned rows are small < 100k, adventureworks 2022 returns > 800K rows
-- depending on the size of your database, the number of tables and size of selected return rows
-- caveat: its best to use the sqlcmd -i and -o to output the possibility of a huge return of tables and records. your gui could be overwhelmed if you try it otherwise. just a word of caution.
-- change the following to your database your database
-- use AdventureWorksDW2022
use {YourDatabase}
SET QUOTED_IDENTIFIER ON
create table #tmpTablesinDB (
T_ROW_ID integer identity,
T_QUALIFIER varchar(100),
T_OWNER varchar(100),
T_NAME varchar(100),
T_TYPE varchar(100),
T_REMARKS varchar(100)
)
GO
insert into #tmpTablesinDB
exec sp_tables
GO
Declare @T_ROW_ID integer
Declare @T_QUALIFIER varchar(255)
Declare @T_OWNER varchar(255)
Declare @T_NAME varchar(255)
Declare @T_TYPE varchar(255)
Declare @T_REMARKS varchar(255)
-- what tables were out there
--select * from #tmpTablesinDB where T_OWNER != 'dbo' and T_OWNER != 'information_schema' and T_OWNER != 'sys' -- AdventureWorks2022 style, exclude dbo.
select * from #tmpTablesinDB where T_OWNER != 'information_schema' and T_OWNER != 'sys' -- normal style where every table is dbo.
-- create cursor to walk through the return result of tables in database with the following conditions
declare temp_cursor CURSOR FOR
--select * from #tmpTablesinDB where T_OWNER != 'dbo' and T_OWNER != 'information_schema' and T_OWNER != 'sys' -- AdventureWorks2022 style, exclude dbo.
select * from #tmpTablesinDB where T_OWNER != 'information_schema' and T_OWNER != 'sys' -- normal style where every table is dbo.
-- open cursor and we are focused on the @T_OWNER and @T_NAME columns
open temp_cursor
fetch next from temp_cursor into @T_ROW_ID, @T_QUALIFIER, @T_OWNER, @T_NAME, @T_TYPE, @T_REMARKS
-- beging cursor
while @@FETCH_STATUS = 0
begin
declare @sql varchar(255)
--swap out comment/uncomment the following two selects for testing.
--why? top 10 rows per table versus all rows per table, select top (10) while running in ssms(t-sql) to view the results and select * doing the sqlcmd -i -o example from cmd prompt
-- reference line 73 line 74, keep top (##) low and do not use the select * in ssms unless database, tables and returned rows are small < 100k, adventureworks 2022 returns > 800K rows
-- select @sql = 'select * from ' + @T_OWNER + '.' + @T_NAME
select @sql = 'select top (5) * from ' + @T_OWNER + '.' + @T_NAME -- AdventureWorks2022 format style
--give me the table name from the sql query
select @sql as [sql_query]
-- give me the results
EXECUTE(@sql)
fetch next from temp_cursor into @T_ROW_ID, @T_QUALIFIER, @T_OWNER, @T_NAME, @T_TYPE, @T_REMARKS
end
GO
close temp_cursor
deallocate temp_cursor
GO
drop table #tmpTablesinDB
GO
-- end SCRIPT #2
-- © 2024 andrew r. flagg
-- attributions and credits:
-- ref 1:
https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file (#2)
-- ref 2:
https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver16
-- ref 3:
https://www.sqlshack.com/exec-sql-overview-and-examples/
-- ref 4:
https://stackoverflow.com/questions/12059327/how-to-execute-a-sql-string-that-references-a-table-variable
Three screenshots for your reference:
-- © 2024 andrew r. flagg
if you found this article helpful, consider contributing $10, 20 an Andrew Jackson or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.
© 2024 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.