SQL Stored Procedure to search a complete SQL DB for a string/keyword
Create below SearchSQLDB Stored Procedure by connecting to the target SQL DB in your SQL MGMT studio. [code language=”sql”] CREATE PROC SeachSQlDB ( @SearchPattern nvarchar(100) ) AS BEGIN CREATE TABLE #TempResults (ColumnName nvarchar(300), ColumnValue nvarchar(3000)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @TempSrchStr nvarchar(110) SET @TableName = ” SET @TempSrchStr = QUOTENAME(‘%’ + @SearchPattern + ‘%’,””) 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’) = […]
Read more