GoGreen PC Tune-Up™
Learn More

Insta-Install™
this is how ssl encrypt our websites
MTNCOMP | List View | Table View | myBlog (1772 Entries)
myBlog Home

Blog


SQL research to return all database tables and their records

SQL

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.