Mastering SQL Server: Tips and Tricks for Efficient Database Exploration

 
Database spelunking
 
Exploring without light….
 
The hardest thing about writing SQL
is figuring out the database relationships.
 
Finding SQL Server instances
 
Just ask SQL Server Browser
Use PowerShell
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
Command prompt
Sqlcmd -L
Only shows instances within a domain
Only shows instances current login has permissions to see
SQL Browser services must be running
 
From Command Prompt
 
From PowerShell
 
Relational databases store database
designs in a database
 
 
ANSI
INFORMATION_SCHEMA
Microsoft
SYS Schema
 
List databases with an instance
 
Query SYS schema
select * from sys.databases
Query INFORMATION_SCHEMA
INFORMATION_SCHEMA doesn’t have a Database object
 
 
Find a table by name
 
In a specific database
Use Management Studio
Query SYS schema
select * from sys.tables where [name] like '%log%‘
Query INFORMATION_SCHEMA
select * from INFORMATION_SCHEMA.tables where TABLE_NAME like '%log%'
In a specific instance
SYS and INFORMATION_SCHEMA have database scope (mostly)
You can only search one database at a time
Use TSQL
Build a cursor of databases, loop to query sys.tables
Sample file: findTableInInstance.sql
Across multiple instances
Use PowerShell
Loop through instances using System.Data.SqlClient to create separate connection for each instance.
Sample file: findTableInInstanceList.ps1
 
Find a column by name
 
In a specific database
Use Management Studio
Query SYS schema
use AdventureWorksDW2012
select t.name, c.name from sys.columns c inner join sys.tables t
on t.object_id = c.object_id
where c.[name] like '%Reseller%'
In a specific instance
SYS and INFORMATION_SCHEMA have database scope (mostly)
Use TSQL
Build a cursor of databases, loop to query sys.tables
Sample file: findColumnWithinInstance.sql
Across multiple instances
Use PowerShell
Loop through instances using System.Data.SqlClient to create separate connection for each instance.
 
Find a string in any stored procedure in
a database
 
select distinct OBJECT_NAME(id)
from syscomments
where OBJECTPROPERTY(id, ‘IsProcedure’) = 1
and [text] like ‘%categoryID%’
order by OBJECT_NAME(id)
 
Find a string in any view in a database
 
use AdventureWorks2012
select distinct OBJECT_NAME(id)
from syscomments
where OBJECTPROPERTY(id, 'isView') = 1
and [text] like '%emp%'
order by OBJECT_NAME(id)
 
 
Find actual Foreign Key relationships
 
USE AdventureWorks2012;
GO
SELECT
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID(‘
Production.BillOfMaterials
');
 
Find potential Foreign Key relationships
 
Build a cursor of tables & columns
For each table
Build a cursor of columns
For each column
Concatenate a list of tables with that column name
Sample file: findColumnWithinInstance.sql
 
 
Profile a table
 
Build a cursor of columns for specified table
For each column
Count number of records, unique values, null values
Concatenate a list of field values for column
Sample file: ProfileTable.sql
Slide Note
Embed
Share

Delve into the world of SQL Server with expert tips on finding instances, listing databases, and searching for tables or columns by name. Discover efficient ways to navigate relational databases using SQL queries, PowerShell, and Management Studio, making database exploration a breeze.


Uploaded on Jul 30, 2024 | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript


  1. Database spelunking Exploring without light .

  2. The hardest thing about writing SQL is figuring out the database relationships.

  3. Finding SQL Server instances Just ask SQL Server Browser Use PowerShell [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() Command prompt Sqlcmd -L Only shows instances within a domain Only shows instances current login has permissions to see SQL Browser services must be running

  4. From Command Prompt

  5. From PowerShell

  6. Relational databases store database designs in a database ANSI INFORMATION_SCHEMA Microsoft SYS Schema

  7. List databases with an instance Query SYS schema select * from sys.databases Query INFORMATION_SCHEMA INFORMATION_SCHEMA doesn t have a Database object

  8. Find a table by name In a specific database Use Management Studio Query SYS schema select * from sys.tables where [name] like '%log% Query INFORMATION_SCHEMA select * from INFORMATION_SCHEMA.tables where TABLE_NAME like '%log%' In a specific instance SYS and INFORMATION_SCHEMA have database scope (mostly) You can only search one database at a time Use TSQL Build a cursor of databases, loop to query sys.tables Sample file: findTableInInstance.sql Across multiple instances Use PowerShell Loop through instances using System.Data.SqlClient to create separate connection for each instance. Sample file: findTableInInstanceList.ps1

  9. Find a column by name In a specific database Use Management Studio Query SYS schema use AdventureWorksDW2012 select t.name, c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_id where c.[name] like '%Reseller%' In a specific instance SYS and INFORMATION_SCHEMA have database scope (mostly) Use TSQL Build a cursor of databases, loop to query sys.tables Sample file: findColumnWithinInstance.sql Across multiple instances Use PowerShell Loop through instances using System.Data.SqlClient to create separate connection for each instance.

  10. Find a string in any stored procedure in a database select distinct OBJECT_NAME(id) from syscomments where OBJECTPROPERTY(id, IsProcedure ) = 1 and [text] like %categoryID% order by OBJECT_NAME(id)

  11. Find a string in any view in a database use AdventureWorks2012 select distinct OBJECT_NAME(id) from syscomments where OBJECTPROPERTY(id, 'isView') = 1 and [text] like '%emp%' order by OBJECT_NAME(id)

  12. Find actual Foreign Key relationships USE AdventureWorks2012; GO SELECT f.name AS foreign_key_name ,OBJECT_NAME(f.parent_object_id) AS table_name ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name ,OBJECT_NAME (f.referenced_object_id) AS referenced_object ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name ,is_disabled ,delete_referential_action_desc ,update_referential_action_desc FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id WHERE f.parent_object_id = OBJECT_ID( Production.BillOfMaterials');

  13. Find potential Foreign Key relationships Build a cursor of tables & columns For each table Build a cursor of columns For each column Concatenate a list of tables with that column name Sample file: findColumnWithinInstance.sql

  14. Profile a table Build a cursor of columns for specified table For each column Count number of records, unique values, null values Concatenate a list of field values for column Sample file: ProfileTable.sql

More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#