Selet for syntax in whole database

Question

Selet for syntax in whole database

gkramer 23701

Ten Centuries

Points: 1381
More actions
May 2, 2016 at 4:28 am

#318692

LS,
One of our developers asked me if there is a way to find out if a sytax is to be found in the database (1TB, 800 tables).
I do have a script to find syntaxes in Procs/Views/Triggers/Functions but I have to search ALL tables and columns as well
DECLARE @command varchar(1000)
SELECT @command = 'USE ? select * from sys.sql_modules where definition like ''%syntax here%'''
EXEC sp_MSforeachdb @command
Can anyone help me out on this (before I have to re-invent the weel again) ???
Regards,
Guus Kramer
The Netherlands

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

Are you looking for a particular part of code? There is no good way to do this other than what you've noted. SQL Server doesn't index or track code in any way. If you need to find if a particular piece of code exists, you'll have to search each database.

If you are looking for a particular object being used, as in a dependency, sys.depends exists, but it isn't reliable. Really any tool, such as SQL Compare, that needs to determine dependencies, must build a tree by searching all objects.

Disclosure: I work for Redgate Software, maker of SQL Compare.

gkramer 23701 Ten Centuries Points: 1381 More actions · Answer 2

Steven,

thanks for your reply.

It is not a part of code I'm looking for (or DDL) but data stored somewhere in one on the tables/columns.

FI - looking for the name "CLARK" I need a script to check each column in each table for find where the name "CLARK" is stored (promping object and column).

Guus

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Here is one way of searching all character type columns in all tables, be careful though as this can be quite hefty for large databases.

😎

USE TEEST;

GO

-- THE PATTERN TO SEARCH FOR

DECLARE @SEARCH_STRING NVARCHAR(100) = N'AB';

DECLARE @SQL_STR NVARCHAR(MAX) = N''

DECLARE @SEARCH_TEMPLATE NVARCHAR(MAX) = N'

UNION ALL

SELECT

N''{{@TABLE_COLUMN}}'' AS TABLE_COLUMN

,N''{{@SEARCH_STRING}}'' AS SEARCH_STRING

,COUNT(*)

FROM {{@TABLE_NAME}}

WHERE {{@COLUMN_NAME}} LIKE ''%{{@SEARCH_STRING}}%''

';

SELECT @SQL_STR =

STUFF((

SELECT

REPLACE(

REPLACE(@SEARCH_TEMPLATE,N'{{@TABLE_COLUMN}}'

,CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(SC.object_id),'[')

,CHAR(46)

,QUOTENAME(OBJECT_NAME(SC.object_id),'[')

,CHAR(46)

,QUOTENAME(SC.name,'[')))

,N'{{@TABLE_NAME}}'

,CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(SC.object_id),'[')

,CHAR(46)

,QUOTENAME(OBJECT_NAME(SC.object_id),'[')))

,N'{{@COLUMN_NAME}}'

,QUOTENAME(SC.name,'['))

,N'{{@SEARCH_STRING}}'

,@SEARCH_STRING)

FROM sys.columns SC

INNER JOIN sys.types ST

ON SC.system_type_id = ST.system_type_id

INNER JOIN sys.tables STAB

ON SC.object_id = STAB.object_id

WHERE

-- ONLY SEARCH THE CHARACTER TYPE COLUMNS

(

ST.name LIKE N'%char%'

OR

ST.name = N'sysname'

)

-- COLUMNS WHICH HAVE AT LEAST THE SAME LENGTH AS THE SEARCH STRING

AND SC.max_length >= LEN(@SEARCH_STRING)

-- EXCLUDE SYSTEM OBJECTS

AND OBJECT_SCHEMA_NAME(SC.object_id) <> N'sys'

-- OBJECT IS USER_TABLE

AND STAB.type = 'U'

FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,12,'')

;

-- DISPLAY THE SEARCH CODE

SELECT @SQL_STR;

-- UNCOMMENT TO RUN THE SEARCH

-- EXECUTE SP_EXECUTESQL @SQL_STR;

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

I've found this tool very useful.

http://www.red-gate.com/products/sql-development/sql-search/

(I don't work for Red-Gate)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gkramer 23701 Ten Centuries Points: 1381 More actions · Answer 5

Luis,

the recomended tool is searching for objects etc but I'm looking for pieces of data somewhere in 1 or more tables within a database.

Guus

gkramer 23701 Ten Centuries Points: 1381 More actions · Answer 6

Eirikur,

I changed a bit of your script but the results are not as expected.

-- THE PATTERN TO SEARCH FOR

DECLARE @SEARCH_STRING NVARCHAR(100) = N'<<search string>>';

DECLARE @SQL_STR VARCHAR(MAX) = N''

DECLARE @SEARCH_TEMPLATE NVARCHAR(MAX) = N' {{@TABLE_NAME}} -- {{@COLUMN_NAME}}

';

executing your script with these changes will promp some tables and all of its columns.

selecting data from this table (select * from tablename) shows me empty tables and tables containing data but in this data the <<search string>> is not to be found....

please your reaction on this.

Regards,

Guus