Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How to retrieve all row from all tables with where condition – MindStick
Ask Question

How to retrieve all row from all tables with where condition

Royce Roy 1861 25 May 2015

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?

mssql server sql server
Royce Roy
Ask by Royce Roy
Updated 25 May 2015

1 Answers

Anonymous User
Anonymous User 25 May 2015
Report Abuse

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.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md