Home > DeveloperSection > Forums > How to retrieve all row from all tables with where condition
Royce Roy
Royce Roy

Total Post:134

Points:938
Posted on    May-25-2015 7:37 AM

 MSSQL Server SQL Server 
Ratings:


 1 Reply(s)
 433  View(s)
Rate this:

I have a database and 10 tables in some tables I have a bit column named Open where I store a 0 if the record is not in use by an user and an 1 if the record is in use.

Well, I need to get all the records from all the tables in my database where the "open" column value is true, or 1.

Is this even possible to do?



ben reitman
ben reitman

Total Post:96

Points:676
Posted on    May-25-2015 8:21 AM

Quick piece of code that gets list of tables within your database. Using a cursor loop through the answers checking it they have the fld named [open] and if it does the build a SQL statement and the execute this SQL string.

CREATE PROCEDURE usp_BulkTableOpenReport

AS

BEGIN

 

    DECLARE @TBLS AS TABLE (REF INT IDENTITY (0,1), TABLENAME NVARCHAR(100), TABLEID BIGINT);

    DECLARE @TBL AS NVARCHAR(100);

    DECLARE @TBLID AS BIGINT;

    DECLARE @SQL AS NVARCHAR(MAX);

    DECLARE @I INT = 0;

    DECLARE @M INT = 0;

    DECLARE @V INT = 0

 

    INSERT INTO @TBLS(TABLENAME,TABLEID)

    SELECT NAME,OBJECT_ID FROM sys.tables

 

    SELECT @M = MAX(REF) FROM @TBLS

 

    WHILE @I <= @M

    BEGIN

        SELECT @TBL = TABLENAME, @TBLID= TABLEID FROM @TBLS WHERE REF = @I

        /* CHECK TO MAKE INSURE THAT A FLD CALLED [OPEN] EXIST. */

        SELECT @V = COUNT(*) FROM SYS.columns WHERE name = 'OPEN' AND  OBJECT_ID = @TBLID

        IF @V != 0

        BEGIN

            SET @SQL = 'SELECT * FROM [' + @TBL + '] WHERE [OPEN] = 1'

            EXEC SP_EXECUTESQL @SQL

        END;

        SET @I = @I + 1

    END;

END

GO

From your c# application exec the query "EXEC usp_BulkTableOpenReport" then loop through the table outputs.


Don't want to miss updates? Please click the below button!

Follow MindStick