Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Pancho507
.
Toggle Highlighting
SQL
CREATE PROCEDURE FindMyData_String @DataToFind NVARCHAR(4000), @ExactMatch BIT = 0 AS SET NOCOUNT ON DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT) INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType) SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type FROM Information_Schema.Columns AS C INNER Join Information_Schema.Tables AS T ON C.Table_Name = T.Table_Name AND C.TABLE_SCHEMA = T.TABLE_SCHEMA WHERE Table_Type = 'Base Table' And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char') DECLARE @i INT DECLARE @MAX INT DECLARE @TableName sysname DECLARE @ColumnName sysname DECLARE @SchemaName sysname DECLARE @SQL NVARCHAR(4000) DECLARE @PARAMETERS NVARCHAR(4000) DECLARE @DataExists BIT DECLARE @SQLTemplate NVARCHAR(4000) SELECT @SQLTemplate = CASE WHEN @ExactMatch = 1 THEN 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) = ''' + @DataToFind + ''' ) Set @DataExists = 1 Else Set @DataExists = 0' ELSE 'If Exists(Select * From ReplaceTableName Where Convert(nVarChar(4000), [ReplaceColumnName]) Like ''%' + @DataToFind + '%'' ) Set @DataExists = 1 Else Set @DataExists = 0' END, @PARAMETERS = '@DataExists Bit OUTPUT', @i = 1 SELECT @i = 1, @MAX = MAX(RowId) FROM @Temp WHILE @i <= @MAX BEGIN SELECT @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName) FROM @Temp WHERE RowId = @i PRINT @SQL EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT IF @DataExists =1 UPDATE @Temp SET DataFound = 1 WHERE RowId = @i SET @i = @i + 1 END SELECT SchemaName,TableName, ColumnName FROM @Temp WHERE DataFound = 1 GO exec FindMyData_string 'heidelberg-instruments', 0
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...