0

I have a database which has many tables What i want to do is retrive all the tables ending with a string "Validate" I am able to return those tables but, how can i query on those returned tables to check for a specific value in a column. Can anyone please help me?

ALTER PROCEDURE [dbo].[sp_validateWinner]
 AS
 BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @querytable nvarchar(max)
set @querytable='SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE ''%Validate%'''

Execute sp_executesql @querytable
END

This is the procedure that returns all the table but i further want to check for values on this table which is something like

select C0R0,C2R0 from Table_Name_ending_with_Validate where(C0R0=1 and C2R0 =22)
Akang Toshi
  • 193
  • 5
  • 17
  • 1
    "Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL." (see: [SQL](https://stackoverflow.com/questions/tagged/sql)) ==> **Please correct the tags!** – Luuk Apr 25 '22 at 16:40
  • 1
    You need dynamic SQL, and the implementation of dynamic SQL is differen in MS-SQL, MySQL, Postgresql,... – Luuk Apr 25 '22 at 16:43
  • 2
    That's not the right pattern for ending with "Validate". – shawnt00 Apr 25 '22 at 16:45
  • @shawnt00 you mean? – Akang Toshi Apr 25 '22 at 17:07
  • 1
    I mean that `'%Validate%'` will find it anywhere in the name, not just at the end. – shawnt00 Apr 25 '22 at 17:10
  • 1
    `SELECT 'select ''' + table_name + ''', C0R0,C2R0 from ' + table_name + ' where(C0R0=1 and C2R0 =22)' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%Validate'` Copy and paste then run interactively? – shawnt00 Apr 25 '22 at 17:11
  • @shawnt00 okay yes i got it. – Akang Toshi Apr 25 '22 at 17:14
  • 1
    Maybe the problem is that all these tables should just be a single table? – shawnt00 Apr 25 '22 at 17:18
  • yes @shawnt00 i think it should be in one Table. I will try to do that now.Als0, i have tried your above code and it shows 'no column name' so better put all the table in one table maybe! Thanks btw. – Akang Toshi Apr 25 '22 at 17:25
  • 1
    You just need to supply an alias if you want to get rid of 'No column name'. I'm just trying to help send you down the right road. None of us knows what the goal is. – shawnt00 Apr 25 '22 at 17:27

0 Answers0