T-SQL: Search all tables in database to find a fieldname
Posted: 11/14/2017 2:55:29 PM
By:
Times Read: 1,978
0 Dislikes: 0
Topic: Programming: .NET Framework

If you need to search all tables in a SQL Server database to find any tables that have a certain field name, this script comes in really handy.

Run this on the database you are searching...

DECLARE @FieldName varchar(64)
SET @FieldName = 'fieldname' -- Set this to the name of the field you are looking for.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%' + @fieldName + '%'
ORDER BY schema_name, table_name;
Rating: (You must be logged in to vote)
Discussion View:
Replies:

T-SQL: Search all tables in database to find a fieldname
Posted: 11/14/2017 2:55:29 PM
By:
Times Read: 1,978
0 Dislikes: 0
Topic: Programming: .NET Framework

This isn't as "pretty" as the "Search all tables" from the parent message, but it does work:

SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%SearchTerm%'

Also, if you're doing a lot of searching, there's a free plugin for SSMS from RedGate called SQL Search that's completely free and does even better than both of these techniques.

Rating: (You must be logged in to vote)