Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Set @SearchStr to a string you are looking for and all text columns of a DB
- will be searched to find that string */
- DECLARE @sql nvarchar(4000)
- DECLARE @SearchStr nvarchar(100)
- SET @SearchStr = 'TYPE What you are looking for here'
- DECLARE @Results TABLE (
- TableName nvarchar(256)
- , 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
- SET @sql = 'SELECT TableName = ''' + @TableName + ''', ''' + @TableName + '.' + @ColumnName
- + ''', LEFT(' + @ColumnName + ', 3630)
- FROM ' + @TableName + ' (NOLOCK) ' + 'WHERE ' + @ColumnName
- + ' LIKE ' + @SearchStr2
- --PRINT @sql
- INSERT INTO @Results
- EXECUTE (@sql
- )
- END
- END
- END
- SELECT * FROM @Results
- create function dbo.FindString( @SearchStr Varchar(256) )
- RETURNS
- @Results TABLE (
- TableName nvarchar(256)
- , ColumnName nvarchar(370)
- , ColumnValue nvarchar(3630)
- )
- AS
- BEGIN
- DECLARE @sql nvarchar(4000)
- --DECLARE @SearchStr nvarchar(100)
- SET @SearchStr = ''
- 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
- SET @sql = 'SELECT TableName = ''' + @TableName + ''', ''' + @TableName + '.' + @ColumnName
- + ''', LEFT(' + @ColumnName + ', 3630)
- FROM ' + @TableName + ' (NOLOCK) ' + 'WHERE ' + @ColumnName
- + ' LIKE ' + @SearchStr2
- --PRINT @sql
- INSERT INTO @Results
- EXECUTE (@sql
- )
- END
- END
- END
- Return
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement