none
How to find specific column and data RRS feed

Odpovědi

  • Hi Khan,

    try this Code.

    USE DATABASE YOUR_DB_NAME                       -- YOUR DATABASE-NAME
    
    DECLARE @SearchStr VARCHAR (MAX);
    SET @SearchStr = 'test'                         -- YOUR SEARCHSTRING in COLUMNS
    
    
    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
    WHERE ColumnName LIKE '%Ename%'         -- YOUR SEARCHSTRING in COLUMN-NAME
    
    DROP TABLE #Results;

    Regards

    Jörg


    • Upravený Joerg_x úterý 22. října 2019 18:12
    • Označen jako odpověď Khan_K úterý 22. října 2019 19:18
    úterý 22. října 2019 18:12
  • Please use your search tools. Here is one you can adapt it to your need. Give it a try.

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

    • Označen jako odpověď Khan_K úterý 22. října 2019 19:18
    úterý 22. října 2019 18:42
    Moderátor

Všechny reakce

  • Loop the database names from your server and find out the table names with column "Ename" and load them to the table variable and then loop that table variable to build the dynamic sql scripts to search for "Test".

    A Fan of SSIS, SSRS and SSAS

    úterý 22. října 2019 18:10
  • Hi Khan,

    try this Code.

    USE DATABASE YOUR_DB_NAME                       -- YOUR DATABASE-NAME
    
    DECLARE @SearchStr VARCHAR (MAX);
    SET @SearchStr = 'test'                         -- YOUR SEARCHSTRING in COLUMNS
    
    
    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
    WHERE ColumnName LIKE '%Ename%'         -- YOUR SEARCHSTRING in COLUMN-NAME
    
    DROP TABLE #Results;

    Regards

    Jörg


    • Upravený Joerg_x úterý 22. října 2019 18:12
    • Označen jako odpověď Khan_K úterý 22. října 2019 19:18
    úterý 22. října 2019 18:12
  • Please use your search tools. Here is one you can adapt it to your need. Give it a try.

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

    • Označen jako odpověď Khan_K úterý 22. října 2019 19:18
    úterý 22. října 2019 18:42
    Moderátor
  • Thanks 
    úterý 22. října 2019 19:18