When was the last time you had to find something within your database? Did it feel more like a child’s game or an arduous task?
What would you say if it could be more like child’s play (like the baby elephant is portraying) than a chore?
Child’s Play
The simplest solution may be to use a tool from a respectable vendor. One such tool that comes to mind is SQL Search from RedGate. You can find out more about that tool here.
SQL Search does require that an application be installed on the machine on which you will be using the search feature. The tool is fine and I will leave it up to you to use or not use it.
Alternative
Instead of installing an application onto your machine, you could always write your own script. The information is readily available within SQL Server for you to find the objects you seek.
For example, if I wanted to find any tables that had a particular column, I could use the following.
[codesyntax lang=”tsql”]
DECLARE @ColName Varchar(128)
SET @ColName = 'yourcolumntosearch'
Select t.name as TableName, c.name as ColumnName, ty.name as DataType, c.max_length
,'('+ convert(varchar,c.precision) +','+ convert(varchar,c.scale)+')' as Precision_Scale
,d.definition as DefaultConstraint
,c.collation_name
From sys.tables t
Inner Join sys.columns c
on t.object_id = c.object_id
Inner Join sys.types ty
on c.system_type_id = ty.system_type_id
Left Outer Join sys.default_constraints d
on d.parent_object_id = c.object_id
and d.parent_column_id = c.column_id
WHERE c.NAME = @ColName
Order By t.name,c.column_id;[/codesyntax]
Granted, this query returns a bit more information than you require. Personally, I like to see the additional information related to the columns as I am doing a search through a database. I always find it interesting to find columns of the same name and intent but to have a different definition within the database.
And if you desire to find code within the database that contains a particular column name, then something like the following could be helpful.
[codesyntax lang=”tsql”]
DECLARE @searchstring VARCHAR(128)
SET @searchstring = '%*%'
SELECT SO.name AS CodeName, st.Query,SO.type_desc
FROM sys.objects AS SO
INNER JOIN sys.sql_modules AS SM
ON SM.object_id = SO.object_id
CROSS APPLY (
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N'--' + NCHAR(13) + NCHAR(10) + sqm.definition + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
),
NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
NCHAR(0),
N''
) AS [processing-instruction(query)]
FROM sys.sql_modules AS sqm
WHERE sqm.object_id = SM.object_id
FOR XML
PATH(''),
TYPE
) AS st(Query)
where definition like @searchstring
order by name[/codesyntax]
Now, what if I want to search code and tables at the same time for a particular column name usage? Well, I could take advantage of the following.
[codesyntax lang=”tsql”]
DECLARE @ColName Varchar(128)
SET @ColName = 'yourcolumnhere';
Select t.name as TableName, c.name as ColumnName, ty.name as DataType, c.max_length
,'('+ convert(varchar,c.precision) +','+ convert(varchar,c.scale)+')' as Precision_Scale
,d.definition as DefaultConstraint
,c.collation_name
,sq.CodeName,sq.Query AS ColNamePresentinCode,sq.type_desc AS CodeType
From sys.tables t
Inner Join sys.columns c
on t.object_id = c.object_id
Inner Join sys.types ty
on c.system_type_id = ty.system_type_id
Left Outer Join sys.default_constraints d
on d.parent_object_id = c.object_id
and d.parent_column_id = c.column_id
CROSS APPLY (SELECT SO.name AS CodeName, st.Query,SO.type_desc
FROM sys.objects AS SO
INNER JOIN sys.sql_modules AS SM
ON SM.object_id = SO.object_id
CROSS APPLY (
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N'--' + NCHAR(13) + NCHAR(10) + sqm.definition + NCHAR(13) + NCHAR(10)
+ N'--' COLLATE Latin1_General_Bin2
)
,NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?')
,NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?')
,NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?')
,NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),NCHAR(11),N'?')
,NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?')
,NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0)
,N''
) AS [processing-instruction(query)]
FROM sys.sql_modules AS sqm
WHERE sqm.object_id = SM.object_id
FOR XML
PATH(''),
TYPE
) AS st(Query)
where definition like '%' + @ColName + '%'
AND SM.definition LIKE '%' + t.name + '%') AS sq
WHERE c.NAME = @ColName
Order By t.name,c.column_id;[/codesyntax]
Conclusion
Now, I have a script that will return a row for each time a column appears in a proc. I can correlate which table and column matches to the proc and get the results I need quickly.
Now, you can take this and have a little fun with it.
