Search All Columns in All Tables for a string

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFindTextInColumns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFindTextInColumns]
GO

CREATE PROCEDURE spFindTextInColumns (@TableName sysname = NULL,
@StringToLookFor varchar(500))
AS
-- Example Calls
-- EXECUTE spFindTextInColumns MyTable, 'tcart' --search a specific table
--
-- EXECUTE spFindTextInColumns default, 'tcart' --search all tables
-- GO

DECLARE @columnName nvarchar(128),
@dateType nvarchar(128),
@row smallint,
@rowCount int,
@sql nvarchar(1000)

DECLARE string_find_cursor CURSOR FAST_FORWARD FOR
SELECT [name]
FROM SYSOBJECTS
WHERE (
OBJECTPROPERTY(id, N'IsMsShipped') = 0
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
) AND (@TableName IS NULL OR [name] = @TableName)
ORDER BY [name]

OPEN string_find_cursor

FETCH NEXT FROM string_find_cursor
INTO @tableName

SET @StringToLookFor = '%' + @StringToLookFor + '%'

WHILE @@FETCH_STATUS = 0
BEGIN
SET @row = 1

SELECT @rowCount = MAX([ORDINAL_POSITION])
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
GROUP BY [ORDINAL_POSITION]

WHILE @row <= @rowCount
BEGIN
SELECT @columnName = '[' + [COLUMN_NAME] + ']',
@dateType = [DATA_TYPE]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_NAME] = @tableName
AND [ORDINAL_POSITION] = @row
ORDER BY [ORDINAL_POSITION]

SET @row = @row + 1

SET @sql = NULL

IF @dateType IN ( N'char', N'varchar', N'text')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', ' + @columnName + ') > 0'
ELSE IF @dateType IN (N'nchar', N'nvarchar', N'ntext')
SET @sql = 'SELECT * FROM ' + @tableName + ' WHERE PATINDEX(''' + @StringToLookFor + ''', CAST(' + @columnName + ' As TEXT)) > 0'

IF @sql IS NOT NULL
BEGIN
SET @sql = 'IF EXISTS(' + @sql + ') PRINT ''[' + @tableName + '].' + @columnName + ''''
--PRINT (@sql)
EXEC (@sql)
END
END

FETCH NEXT FROM string_find_cursor
INTO @tableName
END

CLOSE string_find_cursor
DEALLOCATE string_find_cursor


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




/*EXECUTE spFindTextInColumns default, 'Arun'*/

Convert Number to Word

Create Function No2Word ( @StrNo Varchar(100))
Returns Varchar(100)
As
Begin
Declare @StrRet as Varchar(100)
Declare @Len as Int

Select @Len = Len(@StrNo)

If @Len = 1
Begin
Select @StrRet = Case @StrNo
When '1' Then 'One'
When '2' Then 'Two'
When '3' Then 'Three'
When '4' Then 'Four'
When '5' Then 'Five'
When '6' Then 'Six'
When '7' Then 'Seven'
When '8' Then 'Eight'
When '9' Then 'Nine'
Else ''
End
End
If @Len = 2
Begin
Select @StrRet = Case
When @StrNo = '10' Then IsNull(@StrRet,'') + 'Ten'
When @StrNo = '11' Then IsNull(@StrRet,'') + 'Eleven'
When @StrNo = '12' Then IsNull(@StrRet,'') + 'Twelve'
When @StrNo = '13' Then IsNull(@StrRet,'') + 'Thirteen'
When @StrNo = '14' Then IsNull(@StrRet,'') + 'Fourteen'
When @StrNo = '15' Then IsNull(@StrRet,'') + 'Fifteen'
When @StrNo = '16' Then IsNull(@StrRet,'') + 'Sixteen'
When @StrNo = '17' Then IsNull(@StrRet,'') + 'Seventeen'
When @StrNo = '18' Then IsNull(@StrRet,'') + 'Eighteen'
When @StrNo = '19' Then IsNull(@StrRet,'') + 'Ninteen'
Else IsNull(@StrRet,'')
End

Select @StrRet = Case
When Substring(@StrNo,1,1) = '2' Then IsNull(@StrRet,'') + 'Twenty '
When Substring(@StrNo,1,1) = '3' Then IsNull(@StrRet,'') + 'Thirty '
When Substring(@StrNo,1,1) = '4' Then IsNull(@StrRet,'') + 'Fourty '
When Substring(@StrNo,1,1) = '5' Then IsNull(@StrRet,'') + 'Fifty '
When Substring(@StrNo,1,1) = '6' Then IsNull(@StrRet,'') + 'Sixty '
When Substring(@StrNo,1,1) = '7' Then IsNull(@StrRet,'') + 'Seventy '
When Substring(@StrNo,1,1) = '8' Then IsNull(@StrRet,'') + 'Eighty '
When Substring(@StrNo,1,1) = '9' Then IsNull(@StrRet,'') + 'Ninty '
Else
@StrRet
End

If Convert(Numeric,Substring(@StrNo,1,1)) > 1
Begin
Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))
End

If Substring(@StrNo,1,1) = '0' And Convert(Numeric,Substring(@StrNo,2,1)) > 0
Begin
Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))
End
End
If @Len = 3
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Hundred '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,2))
End
If @Len = 4
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Thousand '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,3))
End
If @Len = 5
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Thousand '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,3))
End
If @Len = 6
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Lack '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,5))
End
If @Len = 7
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Lack '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,5))
End
If @Len = 8
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Crore '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,7))
End
If @Len = 9
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Crore '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,8))
End
Return(@StrRet)
End


-- Select dbo.No2Word('123456789')

List all databases attached to the drives regardless of the database status

SELECT DISTINCT DB_NAME(dbid)
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
GO

-- List all ONLINE databases:
SELECT DISTINCT DB_NAME(dbid)
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
GO

-- Alert if there is any system database on the specific drives:
IF EXISTS (SELECT 1
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) IN ('master','tempdb','msdb','model')
)
BEGIN
SELECT DISTINCT DB_NAME(dbid) AS 'There are system databases on these drives:'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) IN ('master','tempdb','msdb','model')
END
GO

-- List all ONLINE databases attached to the drives, except for system databases:
SELECT DISTINCT DB_NAME(dbid)
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
GO

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
GO
-- Build the sp_attach_db:
-- (I preach everyone against using cursor... so I don't)
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX),
@dbname VARCHAR(200),
@prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''

CREATE TABLE #Attach
(Seq INT IDENTITY(1,1) PRIMARY KEY,
dbname SYSNAME NULL,
fileid INT NULL,
filename VARCHAR(1000) NULL,
TxtAttach VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F'))
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach =
CASE WHEN dbname <> @prevdbname
THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
ELSE @cmd
END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
@prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
@dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x

DROP TABLE #Attach
GO
EXEC sp_attach_db @dbname = N'UserDB1',
@filename1=N'J:\Databases\UserDB1.mdf', -- It was F: before
@filename2=N'J:\Databases\UserDB1.ldf' -- It was F: before

EXEC sp_attach_db @dbname = N'UserDB5',@filename1=N'J:\UserDB5.mdf', -- It was F: before
@filename2=N'C:\UserDB5.ldf',
@filename3=N'J:\UserDB5_1.ndf' -- It was F: before
GO
-- Build the sp_attach_db:
-- In this example, I am assuming that only the drive letter changes, not the whole
-- path of the files. You can modify this script according to your needs:
SET NOCOUNT ON
DECLARE @cmd VARCHAR(MAX),
@dbname VARCHAR(200),
@prevdbname VARCHAR(200)

SELECT @cmd = '', @dbname = ';', @prevdbname = ''
CREATE TABLE #Attach
(Seq INT IDENTITY(1,1) PRIMARY KEY,
dbname SYSNAME NULL,
fileid INT NULL,
filename VARCHAR(1000) NULL,
TxtAttach VARCHAR( MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'E' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'F' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END,
CONVERT(VARCHAR(MAX),'') AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F'))
AND DATABASEPROPERTYEX(DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
ORDER BY dbname, fileid,
CASE SUBSTRING(filename,1,1)
WHEN 'E' THEN 'I' + SUBSTRING(filename,2,LEN(filename))
WHEN 'F' THEN 'J' + SUBSTRING(filename,2,LEN(filename))
ELSE filename
END

UPDATE #Attach
SET @cmd = TxtAttach =
CASE WHEN dbname <> @prevdbname
THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''')
ELSE @cmd
END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''',
@prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
@dbname = dbname
FROM #Attach WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x

DROP TABLE #Attach
GO