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

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)