.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Stored Procedure for searching in sqlserver

Posted By:volvo       Posted Date: November 20, 2013    Points: 25    Category: SQL Query    URL: http://www.dotnetspark.com  

A powerful tool for searching every thing in SQL server database table
 

Sometimes it happens that we forgot that where something was in a database. This Stored Procedure allows for searching every table and every field and return a passed value.It's a powerful tool for searching but not use in Programming.

/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 11/20/2013 20:33:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER
PROC [dbo].[SearchAllTables]

(

@SearchStr nvarchar(100)

)

AS

BEGIN



CREATE
TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))


SET
NOCOUNT
ON


DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName =
''

SET @SearchStr2 =
QUOTENAME('%'
+ @SearchStr +
'%','''')


WHILE @TableName IS
NOT
NULL

BEGIN

SET @ColumnName =
''

SET @TableName =

(

SELECT
MIN(QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE =
'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME)
> @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA)
+
'.'
+
QUOTENAME(TABLE_NAME)

),
'IsMSShipped'

)
= 0

)


WHILE
(@TableName IS
NOT
NULL)
AND
(@ColumnName IS
NOT
NULL)

BEGIN

SET @ColumnName =

(

SELECT
MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA =
PARSENAME(@TableName, 2)

AND TABLE_NAME =
PARSENAME(@TableName, 1)

AND DATA_TYPE IN
('char',
'varchar',
'nchar',
'nvarchar')

AND QUOTENAME(COLUMN_NAME)
> @ColumnName

)


IF @ColumnName IS
NOT
NULL

BEGIN

INSERT
INTO #Results

EXEC

(

'SELECT '''
+ @TableName +
'.'
+ @ColumnName +
''', LEFT('
+ @ColumnName +
', 3630)

FROM '
+ @TableName +
' (NOLOCK) '
+

' WHERE '
+ @ColumnName +
' LIKE '
+ @SearchStr2

)

END

END 

END


SELECT ColumnName, ColumnValue FROM #Results

END

Execute the table as below:

SearchAllTables 'Leave'

 

 





     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend