Find references in all Columns and Tables

sql power tools

SELECT column_name,
       table_name,
       table_schema
FROM   information_schema.columns
WHERE  column_name LIKE '%calendar%'
       AND table_schema IN ( 'dbo' )
ORDER BY table_name, column_name

convert to comma seperated list:

sting_agg(first_name, ',');

https://solutioncenter.apexsql.com/quickly-search-for-sql-database-data-and-objects-in-ssms/

DECLARE
@SearchText varchar(200),
@table varchar(100),
@TableID int,
@ColumnName varchar(100),
@string varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
FOR SELECT name, object_id
FROM sys.objects
WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @table, @TableID;
WHILE
@@FETCH_STATUS
=
0
BEGIN
DECLARE CursorColumns CURSOR
FOR SELECT name
FROM sys.columns
WHERE
object_id
=
@TableID AND system_type_id IN(167, 175, 231, 239);
-- the columns that can contain textual data
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar
OPEN CursorColumns;
FETCH NEXT FROM CursorColumns INTO @ColumnName;
WHILE
@@FETCH_STATUS
=
0
BEGIN
SET @string = 'IF EXISTS (SELECT * FROM '
+ @table
+ ' WHERE '
+ @ColumnName
+ ' LIKE ''%'
+ @SearchText
+ '%'') PRINT '''
+ @table
+ ', '
+ @ColumnName
+ '''';
EXECUTE (@string);
FETCH NEXT FROM CursorColumns INTO @ColumnName;
END;
CLOSE CursorColumns;
DEALLOCATE CursorColumns;
FETCH NEXT FROM CursorSearch INTO @table, @TableID;
END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

DECLARE
@SearchText varchar(200) = 'Some text to search for...',
@table varchar(100) = 'SOME_TABLE',
@TableID int,
@ColumnName varchar(100),
@string varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
FOR SELECT name, object_id
FROM sys.objects
WHERE type = 'U';
OPEN CursorSearch
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @table, @TableID;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CursorColumns CURSOR
FOR SELECT name
FROM sys.columns
WHERE object_id = @TableID AND system_type_id IN(167, 175, 231, 239);
-- the columns that can contain textual data
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar
OPEN CursorColumns;
FETCH NEXT FROM CursorColumns INTO @ColumnName;
WHILE
@@FETCH_STATUS = 0
BEGIN
SET @string = 'IF EXISTS (SELECT * FROM '
+ @table
+ ' WHERE '
+ @ColumnName
+ ' LIKE ''%'
+ @SearchText
+ '%'') PRINT '''
+ @table
+ ', '
+ @ColumnName
+ '''';
EXECUTE (@string);
FETCH NEXT FROM CursorColumns INTO @ColumnName;
END;
CLOSE CursorColumns;
DEALLOCATE CursorColumns;
FETCH NEXT FROM CursorSearch INTO @table, @TableID;
END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

Find # of DBs on Server
SELECT COUNT(*) FROM sys.databases;

Find # of Tables in DB
SELECT COUNT(*) 'Number of Tables'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

Find # of Stored Procedures in DB
SELECT COUNT(*) 'Number of Stored Procedures'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

Find # of Views in DB
SELECT COUNT(*) 'Number of Views'
FROM INFORMATION_SCHEMA.VIEWS

Select stored proc into table variable

declare @overdueCourseReport TABLE(
	Division NVARCHAR(50), 
	Course_ID NVARCHAR(50), 
	Course_Title NVARCHAR(100), 
	Course_Manager NVARCHAR(75), 
	Employee_ID NVARCHAR(50), 
	Employee NVARCHAR(75), 
	Active_Flag NVARCHAR(25),
	Employee_Title NVARCHAR(75),
	Supervisor NVARCHAR(75),
	Hire_Date DATETIME,
	[Status] NVARCHAR(75),
	Completed_Date DATETIME,
	Expiration_Date DATETIME,
	Assigned_Date DATETIME,
	Target_Date DATETIME,
	Expired_Course DATETIME,
	Expired_Course_Check NVARCHAR(25),
	Newly_Assigned_Course DATETIME,
	Newly_Assigned_Course_Check NVARCHAR(25),
	Newly_Hired_Employee DATETIME,
	Newly_Hired_Employee_Check NVARCHAR(25),
	Data_As_Of_Date DATETIME,
	Reportable_Reason NVARCHAR(10));

INSERT INTO @overdueCourseReport
EXEC sp_rpt_Overdue_Courses

SELECT * FROM @overdueCourseReport

WITH CTE AS (
    SELECT 
        CAST(DeleteDate AS DATE) as [Date], 
        COUNT(DISTINCT IntUserID) as NumberOfUsers,
        IntCurlmID
    FROM 
        your_table_name
    GROUP BY 
        CAST(DeleteDate AS DATE), IntCurlmID
)
SELECT 
    [Date],
    SUM(NumberOfUsers) as TotalUsers,
    COUNT(DISTINCT IntCurlmID) as NumberOfCourses
FROM 
    CTE
GROUP BY 
    [Date]
ORDER BY 
    [Date] DESC;

SELECT @Sql 
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
ELSE ''
END + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

-- Remove trailing comma and close parenthesis
	SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + ')'

Server Metrics

DECLARE @DatabaseName NVARCHAR(128)
DECLARE @DynamicSQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')

-- Create a table to store results
CREATE TABLE #DatabaseMetrics (
    DatabaseName NVARCHAR(128),
    NumberOfTables INT,
    NumberOfStoredProcedures INT,
    NumberOfViews INT
)

-- Loop through the databases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @DynamicSQL = 'USE ' + @DatabaseName + ';
    DECLARE @TableCount INT, @StoredProcedureCount INT, @ViewCount INT;
    
    SELECT @TableCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'';
    SELECT @StoredProcedureCount = COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'';
    SELECT @ViewCount = COUNT(*) FROM INFORMATION_SCHEMA.VIEWS;
    
    INSERT INTO #DatabaseMetrics (DatabaseName, NumberOfTables, NumberOfStoredProcedures, NumberOfViews)
    VALUES (''' + @DatabaseName + ''', @TableCount, @StoredProcedureCount, @ViewCount);'

    -- Execute the dynamic SQL
    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Close and deallocate the cursor
CLOSE db_cursor  
DEALLOCATE db_cursor

-- Display results
SELECT * FROM #DatabaseMetrics
ORDER BY NumberOfTables DESC, NumberOfStoredProcedures DESC, NumberOfViews DESC

-- Drop the temporary table
DROP TABLE #DatabaseMetrics

Here's what the script does:

1. It declares a cursor to loop through each database on your SQL Server instance.
2. It creates

This technique can be referred to as "Side-by-Side Column Display Using Row Numbers".

Blog Entry:

---

Side-by-Side Column Display Using Row Numbers

When working with datasets that contain disparate distinct value counts across columns, it's often challenging to display each column's distinct values side by side. Traditional techniques, such as simple JOIN operations, might truncate the displayed data to the count of the smallest column.

The "Side-by-Side Column Display Using Row Numbers" technique addresses this problem elegantly. By leveraging the power of the ROW_NUMBER() function, we can assign a unique sequence number to each distinct value within every column. These row numbers then serve as a makeshift index, allowing us to align the columns side by side using LEFT JOIN operations.

To ensure we have a comprehensive row number set that covers the largest distinct count, we can create a temporary sequence of numbers larger than our largest distinct count. This ensures every distinct value from each column gets a place in the final display.

This approach is powerful, especially in scenarios where you want to visualize or compare distinct values from multiple columns in one view. Remember, the result might have NULL values for columns where the number of distinct values is less than the maximum among the columns.

---

You can adjust and expand upon this summary to better fit the tone and style of your blog.

Side-by-Side Column Display

WITH
	NumberSequence AS (
		SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
		FROM master.dbo.spt_values s1, master.dbo.spt_values s2
	),
	CSOEngineerID AS (
		SELECT 
			CSOEngineerID
			, ROW_NUMBER() 
			OVER (ORDER BY CSOEngineerID) 
		AS rn
		FROM (SELECT DISTINCT(CSOEngineerID) FROM TechPubsRequestAdditional a) AS a
	),
	EngineerID AS (
		SELECT 
			EngineerID
			, ROW_NUMBER() 
			OVER (ORDER BY EngineerID) 
		AS rn
		FROM (SELECT DISTINCT(EngineerID) FROM TechPubsRequestAdditional a) AS b
	),
	CSOProgramManagerID AS (
		SELECT 
			CSOProgramManagerID
			, ROW_NUMBER() 
			OVER (ORDER BY CSOProgramManagerID) 
		AS rn
		FROM (SELECT DISTINCT(CSOProgramManagerID) FROM TechPubsRequestAdditional a) AS c
	),
	ProgramManagerID AS (
		SELECT 
			ProgramManagerID
			, ROW_NUMBER() 
			OVER (ORDER BY ProgramManagerID) 
		AS rn
		FROM (SELECT DISTINCT(ProgramManagerID) FROM TechPubsRequestAdditional a) AS d
	)

	SELECT
		*
	FROM NumberSequence _
	LEFT JOIN CSOEngineerID a ON _.rn = a.rn
	LEFT JOIN EngineerID b ON _.rn = b.rn
	LEFT JOIN CSOProgramManagerID c ON _.rn = c.rn
	LEFT JOIN ProgramManagerID d ON _.rn = d.rn
        --WHERE _.rn < 75
	WHERE a.rn IS NOT NULL OR b.rn IS NOT NULL OR c.rn IS NOT NULL OR d.rn IS NOT NULL

Find which Stored Procedures are calling specified "TableName"

DECLARE @TableName NVARCHAR(256) = 'TableToFind';

SELECT 
	DISTINCT OBJECT_NAME(sm.object_id) AS ProcedureName
FROM sys.sql_modules AS sm
JOIN sys.procedures sp ON sm.object_id = sp.object_id
WHERE sm.definition LIKE '%' + @TableName + '%'
AND TYPE = 'p'

Find which Tables the specified Column is in

SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'IntCrsID'
ORDER BY t.name

Advanced Table Finder - 1

-- Look in all Tables for specified Column
-- Then, filter those Tables based on Column value
SELECT DISTINCT t.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name IN (
SELECT t.name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'IntCrsID'
--ORDER BY t.name
)
AND c.name LIKE '%Date%'

Advanced Table Finder - 2

-- Find all the Tables that have:
-- 1. Column = IntCrsID
-- 2. Column LIKE '%Date%'
-- 3. Column = IntUserID
-- so I can try to find which table has bad Date data 
-- and not have to deep dive through application code
SELECT t.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name IN (
					SELECT DISTINCT t.name
					FROM sys.tables t
					JOIN sys.columns c ON t.object_id = c.object_id
					WHERE t.name IN (
										SELECT t.name
										FROM sys.columns c
										JOIN sys.tables t ON c.object_id = t.object_id
										WHERE c.name = 'IntCrsID'
									)
					AND c.name LIKE '%Date%'
				)
AND c.name LIKE 'IntUserID'
ORDER BY t.NAME ASC;

Advanced Table Finder - 3

Finds anything in any DB dynamically

-- Now look at all tables for specific User, Course
SELECT t.name AS TableName
INTO #Temp
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name IN (
					SELECT DISTINCT t.name
					FROM sys.tables t
					JOIN sys.columns c ON t.object_id = c.object_id
					WHERE t.name IN (
										SELECT t.name
										FROM sys.columns c
										JOIN sys.tables t ON c.object_id = t.object_id
										WHERE c.name = 'IntCrsID'
									)
					AND c.name LIKE '%Date%'
				)
AND c.name LIKE 'IntUserID'

--SELECT * FROM #Temp;

DECLARE @TableName NVARCHAR(256);
DECLARE @DynamicSql NVARCHAR(MAX);
DECLARE @IntUserID INT = 6004437;
DECLARE @IntCrsID INT = 70792;

DECLARE TableCursor CURSOR FOR
SELECT TableName FROM #Temp

	OPEN TableCursor
	FETCH NEXT FROM TableCursor INTO @TableName

	WHILE @@FETCH_STATUS = 0

	BEGIN
		SET @DynamicSql = 'SELECT * FROM [' + @TableName + '] WHERE IntUserID = ' + CONVERT(NVARCHAR(10), @IntUserID) + ' AND IntCrsID = ' + CONVERT(NVARCHAR(10), @IntCrsID);
		
		EXEC sp_executesql @DynamicSql;

		FETCH NEXT FROM TableCursor INTO @TableName
	END

	CLOSE TableCursor
	DEALLOCATE TableCursor

DROP TABLE #Temp;

Version of SQL

SELECT 
	SERVERPROPERTY('ProductVersion') AS 'Version'
	, SERVERPROPERTY('ProductLevel') AS 'Level'
	, SERVERPROPERTY('Edition') AS 'Edition';

Look in all Audit Tables, find all tables where a particular column value = X

-- Look in all Audit Tables, find all tables where a particular column value = X
-- Declare variables
DECLARE @TableName NVARCHAR(128), @ColumnName NVARCHAR(128), @TableSchema NVARCHAR(64), @Sql NVARCHAR(MAX), @RecordCount INT

-- Cursor to store tables that don't start with 'audit_'
DECLARE TableCursor CURSOR FOR
SELECT table_name, table_schema, column_name
FROM information_schema.columns
WHERE column_name LIKE '%IntCrsID%'
  AND table_schema = 'dbo'
  --AND table_name NOT LIKE 'Audit_%'

-- Open cursor
OPEN TableCursor

-- Loop through all tables
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
	-- First, check if there are any records
    SET @Sql = 'SELECT @CountOUT = COUNT(*) FROM [Genusus_Audit].[dbo].[Audit_' + @TableName + '] WHERE ' + @ColumnName + ' = 64440'
    EXEC sp_executesql @Sql, N'@CountOUT INT OUTPUT', @RecordCount OUTPUT

	 -- If records are found, then execute the SELECT statement
    IF @RecordCount > 0
    BEGIN
		-- Construct the SQL statement
		SET @Sql = 'SELECT ''' + @TableName + ''' AS SourceTable, * FROM [Genusus_Audit].[dbo].[Audit_' + @TableName + '] WHERE ' + @ColumnName + ' = 64440 ORDER BY SSKModifiedDate DESC'

		-- Execute the SQL statement
		EXEC sp_executesql @Sql
	END

    -- Get the next table
    FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema, @ColumnName
END

-- Clean up
CLOSE TableCursor
DEALLOCATE TableCursor

To find all tables that have a particular column, you can use the following SQL query:

SELECT 
    TABLE_NAME, 
    COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    COLUMN_NAME = 'YourColumnName';

Replace 'YourColumnName' with the name of the column you are looking for.

To find all stored procedures that reference a particular column, you can use this query:

SELECT 
    ROUTINE_NAME, 
    ROUTINE_TYPE, 
    ROUTINE_DEFINITION
FROM 
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    ROUTINE_DEFINITION LIKE '%YourColumnName%'
    AND ROUTINE_TYPE = 'PROCEDURE';

Replace 'YourColumnName' with the name of the column you are looking for. This query searches for stored procedures that contain the column name within their definition.

Both queries should be run in SQL Server Management Studio (SSMS). Let me know if you need further customization or assistance!

[Azure Data Factory | Copy multiple tables in Bulk with Lookup & ForEach](https://www.youtube.com/watch?v=KsO2FHQdILs)