SQL-Server : Find value of a column in entire database

Some times we need to find one column is present in how many tables with the same value. For example I have employee database. In this empid column is present in 7 table. Now I want to check empid 101 is present in how many table. This we can find with below stored procedure.

CREATE PROCEDURE GetColumnValueInDatabase 
        @value VARCHAR(max) --value which we want to search

       ,@searchColumn VARCHAR(250)--specify the column name for which we need to search the value

AS

BEGIN

       DECLARE @qry VARCHAR(max)

       DECLARE @tabl TABLE (

              table_name VARCHAR(350)

              ,columnname VARCHAR(350)

              ,isprocessed BIT

              ,tableschema VARCHAR(5)

              )

       DECLARE @tabl_1 TABLE (

              table_name VARCHAR(350)

              ,columnname VARCHAR(350)

              )

       INSERT INTO @tabl

       SELECT tbls.TABLE_NAME

              ,cols.COLUMN_NAME

              ,0

              ,tbls.TABLE_SCHEMA

       FROM INFORMATION_SCHEMA.TABLES AS tbls

       JOIN INFORMATION_SCHEMA.COLUMNS AS cols ON tbls.TABLE_NAME = cols.TABLE_NAME

       WHERE cols.COLUMN_NAME = @searchColumn

       DECLARE @table_name VARCHAR(350)

              ,@columnname VARCHAR(350)

              ,@tblSchema VARCHAR(5)

       WHILE EXISTS (

                     SELECT 1

                     FROM @tabl

                     WHERE isprocessed = 0

                     )

       BEGIN

              SELECT TOP 1 @table_name = table_name

                     ,@columnname = columnname

                     ,@tblSchema = tableschema

              FROM @tabl

              WHERE isprocessed = 0

              ORDER BY table_name DESC

              SET @qry = 'SELECT ''' + @table_name + ''', ''' + @columnname + ''' FROM ' + @tblSchema + '.' + @table_name + ' where ' + @columnname + ' = ' + @value

              PRINT (@qry)

              INSERT @tabl_1

              EXEC (@qry)

              UPDATE @tabl

              SET isprocessed = 1

              WHERE table_name = @table_name

                     AND columnname = @columnname

       END

       SELECT *

       FROM @tabl_1

END

Example

EXEC GetColumnValueInDatabase 101,'empid'

Thx,
RS

SQL Server - Enabling Service Broker

In this post I will cover to enable the service broker id and solution of some error related to this.

With the help of below query we can see what is the service broker id of database and whether it is enabled or not.

SELECT

    is_broker_enabled AS IsServiceBrokerEnabled,

    service_broker_guid AS ServiceBrokerGUID,

    name AS DatabaseName,

    database_id AS DatabaseId

FROM sys.databases

In below screen shot highlighted row shows that service borker is not enabled for the database TestDatabase.

We can enable the service broker queue with the help of below query

ALTER DATABASE Database_Name SET ENABLE_BROKER;

If you have database which is huge in size then above query may take time. In that case you can use the below query.

ALTER DATABASE Database_Name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Now, some time we are getting the error 

Msg 9772, Level 16, State 1, Line 1 
The Service Broker in database "Database_Name" cannot be enabled because there is already an enabled Service Broker with the same ID.

This error is coming because there is already one database which is having the same service broker id and system is trying to enable another database with the same service broker id. The solution of this error is to generate the new service broker id for the database. This we can achieve with below query

ALTER DATABASE Database_Name SET NEW_BROKER;

If you have database which is huge in size then above query may take time. In that case you can use the below query.

ALTER DATABASE [Database_name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Thx,

RS