database-schema

Feldinhalt finden

CREATE PROC 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

Quelle: https://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*