[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic SQL and Search of string in ALL database objects

Posted on 2011-05-11
9
Medium Priority
?
377 Views
Last Modified: 2012-05-11
I have a stored procedure that I want to be able to search any one or ALL databases on my SQL Server Instance and find the string I'm passing in.

My attached code works...kind of.

I need to use the @DatabaseName name I'm passing and search THAT databases schema, sysobjects, tables for my @SearchString

The code below...returns the search ONLY on that database this particular Stored Procedure is in.
CREATE PROCEDURE sp_utils_DatabaseAnySearchStringGet (
						@DatabaseName  varchar(30), 
						@SearchString varchar(100))

AS
BEGIN

Declare @sql varchar(max)
Set @sql = '
	--Find string within Stored Procedures or Views	
	select ''' + @DatabaseName + ''' as db, o.name, o.xtype, o.type, text
	from sysobjects o
	inner join syscomments c on o.id = c.id
	and c.text like ''%'' + ''' + @SearchString + ''' + ''%''
	order by o.name

	--Find string within Tables
	SELECT ''' + @DatabaseName + ''' as db, 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 ''%'' + ''' + @SearchString + ''' + ''%''
	ORDER BY schema_name, table_name;'
	
	print @sql
	--exec(@sql)
END
GO

Open in new window

0
Comment
Question by:lrbrister
  • 3
  • 3
  • 3
9 Comments
 
LVL 11

Accepted Solution

by:
brutaldev earned 2000 total points
ID: 35742048
You can use the information_schema views directly off the database: http://msdn.microsoft.com/en-us/library/ms186778.aspx

Here is an example for your use case, you will need to change this as required:
 
CREATE PROCEDURE sp_utils_DatabaseAnySearchStringGet (
                                                @DatabaseName  varchar(30), 
                                                @SearchString varchar(100))

AS
BEGIN
Declare @sql varchar(max)
Set @sql = '
        --Find string within Stored Procedures
        select ''' + @DatabaseName + ''' as db, r.SPECIFIC_NAME, r.ROUTINE_DEFINITION
        from ' + @DatabaseName + '.information_schema.routines r
        where r.ROUTINE_DEFINITION like ''%'' + ''' + @SearchString + ''' + ''%''
        order by r.SPECIFIC_NAME
        
        --Find string within Views
        select ''' + @DatabaseName + ''' as db, v.TABLE_NAME, v.VIEW_DEFINITION
        from ' + @DatabaseName + '.information_schema.views v
        where v.VIEW_DEFINITION like ''%'' + ''' + @SearchString + ''' + ''%''
        order by v.TABLE_NAME

        --Find string within Tables
        select ''' + @DatabaseName + ''' as db, c.TABLE_NAME, c.TABLE_SCHEMA, c.COLUMN_NAME
        from ' + @DatabaseName + '.information_schema.columns c
        where c.COLUMN_NAME like ''%'' + ''' + @SearchString + ''' + ''%''
        order by c.TABLE_SCHEMA, c.TABLE_NAME'
        
        print @sql
        --exec(@sql)
END
GO

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35742465
It sounds like your issue is not in getting the code to work, but the fact that INFORMATION_SCHEMA views are for the database in which the procedure is stored.  Try making the sp a system stored procedure.  See Tim's Article on 'how to': http://www.sqlservernation.com/home/create-a-system-stored-procedure.html

EXECUTE sp_ms_marksystemobject 'sp_utils_DatabaseAnySearchStringGet'

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35742491
Hmmm.  And guess that doesn't really solve the issue of running in one database and getting results from another, but it at least removes the need to define the procedure in every database on your server.  You will still, however, need to change to the database you want to check against unfortunately.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:brutaldev
ID: 35743991
You can run the procedure on any database and send in any other database name because of this dynamic SQL: from ' + @DatabaseName + '.information_schema.routines

Sounds like the user wants to run this proc from one place and be able to pass in the database name that the search must be performed on. prefixing the database name means you can get the schema of any database not only the database the procedure is installed on.

EG: To get tables on the master database while using the model database this will work and only returns objects for master:
 
USE model
GO

-- Get table on the master database regardless of the database in use.
SELECT * FROM master.information_schema.tables
GO

-- Get the columns from the AdventureWorks database
SELECT * FROM AdventureWorks.information_schema.columns
GO

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 35745623
Worked perfectly.
Thanks
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35746786
@brutaldev: I didn't catch that you had changed the code to add the 3 part naming.  By your comment it sounded like you just changed from sys tables to INFORMATION_SCHEMA views. I agree that will work better.  Good work!
0
 

Author Comment

by:lrbrister
ID: 35746888
mwvisa1: and brutaldev:

Yeah..our crystal report writers are at the mercy of some SQL folks and spend a lot of time tracking down a column or a view...etc..

They absolutely love this stored procedure...added a litle interface...they type in their string...viola! They get a list of results to use.
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35748745
@lrbrister: Good to know, just make sure that that someone doesn't try to do some SQL injection (at least replace any inserted single quote ' to two single quotes '' to prevent it messing up your dynamic SQL).

@mwvisa1: Thanks for the response, always nice to hear from such a highly ranked expert.
0
 

Author Comment

by:lrbrister
ID: 35748758
brutaldev:
Understood...and done.
Our SQL Servers are internal only and they have stated that they "would never be visible outside our network"...but I play it on the safe side anyway.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question