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

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)