TSQL - Search Column and its Value from ALL tables in database

-
Hi Guys,

Sometimes we try to find a specific Column Name from all the tables in the database. Below is the query-  
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name like '%Column_Name%'
ORDER BY schema_name, table_name

Of course everyone knows about it, you can google it.
But below is the query to find a  Column Name and its Value  from all the tables in database. Isn’t interesting !!

CREATE PROC SearchColumnAndItsValueFromAllTables 
( 
    @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', 'int', 'decimal') 
                    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


Still this query can be optimized and extended further. But it helps to most of the developers. J

Cheers!!



Comments

Post a Comment

Popular posts from this blog

ASP.NET Compillation Error BC31007 - Solution

-
TToday while I am working on the ASP.NET application, I suddenly encountered with below error while trying to debug the application. I tried couple of actions: .    ·           Clean and rebuild solution ·          IDE restart ·          Temporary asp.net files cleanup ·          System restart ·          Modified Identity in App pool Finally it got resolved by modifying the Identity to “Local System” in Application Pool Server Error in '/My App’ Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC31007 : Unable to open module file 'C:\Windows\Microsoft.NET\Framework64\v4...
Read more

Test & Debug WCF service using WCFTestClient.exe

-
Image
VS.NET 2010 provides WCFTestClient.exe for us to test and debug the WCF service.  Please follow me to test and debug the service. Generally, first debug and then test the service, but here first I will tell you how to test it and then how to debug it J Test the WCF service using WCFTestClient. 1.        Open “Visual Studio Command Prompt (2010)” 2.        Go to “c:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\” 3.        Run WcfTestClient.exe 4.        It will open WCF Test Client, now add WCF Service (URL) 5.        Select the service method, from left panel 6.        Provide request data (i.e. values to parameters) on right panel and click on Invoke button. It gives you the response. Debug the WCF service, follow the commands. 1.     ...
Read more

Microsoft Surface in brief

-
After years of covert development, Microsoft says it will release a computer that uses the tabletop as its high-resolution display, recognizes objects placed on the surface and skips the traditional keyboard and mouse in favor of fingers on the screen. What it is : A computer in the form of a table, using the hard acrylic tabletop as a high-resolution screen. First product from Microsoft's previously secret Surface Computing team, which has 120 employees. How it works : The surface itself isn't touch-sensitive, but a series of cameras inside the table can see when someone places or drags a finger, hand or any other object on or across the tabletop screen. Internal projector lights screen from beneath. Interface : People can use their hands to touch and move virtual objects on the screen, just as they would with a mouse on a traditional PC. The system also can recognize objects placed on the surface, based on their shape or on special codes affixed to them.Size: 22 inches high, ...
Read more