How to retrieve all row from all tables with where condition

Total Post:149

Points:1043
 957  View(s)
Ratings:
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?

  1. Post:96

    Points:676
    Re: How to retrieve all row from all tables with where condition

    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.

      Modified On Apr-09-2018 11:25:12 PM

Answer