Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Hide ‘n Seek

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?

Child's Game or 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.

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;

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.

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

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.

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;

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.

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...