1…Works like a charm:
1: DECLARE @SearchStr nvarchar(100)
2:
3: SET @SearchStr = 'SEARCH_KEYWORD'
4:
5: CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
6:
7: SET NOCOUNT ON
8:
9: DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
10: SET @TableName = ''
11: SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
12:
13: WHILE @TableName IS NOT NULL
14: BEGIN
15: SET @ColumnName = ''
16: SET @TableName =
17: (
18: SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
19: FROM INFORMATION_SCHEMA.TABLES
20: WHERE TABLE_TYPE = 'BASE TABLE'
21: AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
22: AND OBJECTPROPERTY(
23: OBJECT_ID(
24: QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
25: ), 'IsMSShipped'
26: ) = 0
27: )
28:
29: WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
30: BEGIN
31: SET @ColumnName =
32: (
33: SELECT MIN(QUOTENAME(COLUMN_NAME))
34: FROM INFORMATION_SCHEMA.COLUMNS
35: WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
36: AND TABLE_NAME = PARSENAME(@TableName, 1)
37: AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
38: AND QUOTENAME(COLUMN_NAME) > @ColumnName
39: )
40:
41: IF @ColumnName IS NOT NULL
42: BEGIN
43: INSERT INTO #Results
44: EXEC
45: (
46: 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
47: FROM ' + @TableName + ' (NOLOCK) ' +
48: ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
49: )
50: END
51: END
52: END
53:
54: SELECT ColumnName, ColumnValue FROM #Results
2….Here is another TSQL code which helps in searching all stored procedures:
1: SELECT Name FROM sys.procedures
2: WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%ProcessLog%'
OR use (this will search sprocs, udfs, triggers etc. (not just sprocs)):
1: SELECT object_name(id) FROM syscomments where text like '%ProcessLog%'
3….. Here is another SQL which is helpful. If you have a lot of columns in a table and you need to find columns which are of a particular data type (e.g. datetime) then we can use this:
1: SELECT * FROM SYSCOLUMNS WHERE Id = OBJECT_ID('geneva_security_extract') AND XTYPE = 61
. you pass in the name of the table to the OBJECT_ID function. Hope this helps