The problem con this code, is that are many tables and the name of tables are distinct. Example: TablenMed, TablenMedTrans, TablenMedRigid, etc. The loop no apply. Thanks
Doesn't sound like a problem, sounds like an opportunity to have a lookup table that has all the table names you need to query and you can then create a look around the results of those table names create your UNION query like I showed before.
For instance:
TableName table could have columns: TableID PD int, TblName nvarchar(50), CreateDate datetime, IsActive bit (I didn't put the TblName field as a PK because you couldn't change it without a lot of extra work)
Then your simple query would be: SELECT UNIQUE TblName FROM TableName WHERE IsActive = 1. Then loop through the results of that query and create your UNION statement OR just create the query, run it, and merge the results into your existing datatable in
code.
Either way, no problem, sounds like it might be a processing hog though.
Remember to mark as answer if this post answered or solved your problem.
It's a bit messy, but you could also use something like this:
Declare @MyQuery varchar(max);
WITH Cte AS (
SELECT Cast('' AS varchar(max)) Name, Cast(0 as int) RowNum
UNION ALL
SELECT cte.name + ' UNION ALL SELECT Id FROM ' + a.Name, a.RowNum
FROM (
SELECT Cast(Tables.Name AS varchar(max)) Name, Cast(Row_Number() OVER (ORDER BY Tables.Name) as int) RowNum
FROM sys.Tables
WHERE object_Id IN (
SELECT object_Id
FROM sys.Columns
WHERE name = 'Id'
)
AND Tables.Name LIKE 'Table%'
) a
INNER JOIN Cte
ON a.RowNum = Cte.RowNum + 1
)
SELECT Substring(Name, CharIndex('SELECT', Name), 9999999)
FROM Cte
WHERE Cte.RowNum = (SELECT Max(RowNum) FROM Cte);
Exec(@MyQuery);
Marked as answer by Chen Yu - MSFT on Aug 22, 2012 06:14 AM
I was thinking of that as well Matts...only downfall is getting the table names. If there are 1000+ tables my guess is there probably isn't a determining factor to tell which tables the OP needs to get. Maybe if there was a naming convention that could
be queryable like Products.ProductDesc, Products.Product, Product.ProductImages, etc. you could search for all Products.% schema. Looking for a column name of "ID" could return unexpected results, not saying it couldn't work, just probably need additional
verification you are getting the correct tables.
Or if there were a property you could set on the table and query that but then again I don't know if that can even be done.
Remember to mark as answer if this post answered or solved your problem.
Marked as answer by Chen Yu - MSFT on Aug 22, 2012 06:14 AM
Trover
Member
34 Points
22 Posts
Use like or '% for query object database
Aug 18, 2012 04:31 PM|LINK
Hi:
I need make a query for 3 tables, but I want the result in one column, example:
Select Id from Table%
Where id exist in 3 tables: Table1, table2 and tbl3. In my real problem i have more 1000 tables
b471code3
Star
13877 Points
2598 Posts
Re: Use like or '% for query object database
Aug 19, 2012 04:36 AM|LINK
You could generate your SQL dynamically in a loop and just use UNION
Trover
Member
34 Points
22 Posts
Re: Use like or '% for query object database
Aug 19, 2012 06:09 AM|LINK
The problem con this code, is that are many tables and the name of tables are distinct. Example: TablenMed, TablenMedTrans, TablenMedRigid, etc. The loop no apply. Thanks
b471code3
Star
13877 Points
2598 Posts
Re: Use like or '% for query object database
Aug 19, 2012 02:23 PM|LINK
Doesn't sound like a problem, sounds like an opportunity to have a lookup table that has all the table names you need to query and you can then create a look around the results of those table names create your UNION query like I showed before.
For instance:
TableName table could have columns: TableID PD int, TblName nvarchar(50), CreateDate datetime, IsActive bit (I didn't put the TblName field as a PK because you couldn't change it without a lot of extra work)
Then your simple query would be: SELECT UNIQUE TblName FROM TableName WHERE IsActive = 1. Then loop through the results of that query and create your UNION statement OR just create the query, run it, and merge the results into your existing datatable in code.
Either way, no problem, sounds like it might be a processing hog though.
MattsDotNetU...
Contributor
3178 Points
515 Posts
Re: Use like or '% for query object database
Aug 20, 2012 05:54 PM|LINK
It's a bit messy, but you could also use something like this:
Declare @MyQuery varchar(max); WITH Cte AS ( SELECT Cast('' AS varchar(max)) Name, Cast(0 as int) RowNum UNION ALL SELECT cte.name + ' UNION ALL SELECT Id FROM ' + a.Name, a.RowNum FROM ( SELECT Cast(Tables.Name AS varchar(max)) Name, Cast(Row_Number() OVER (ORDER BY Tables.Name) as int) RowNum FROM sys.Tables WHERE object_Id IN ( SELECT object_Id FROM sys.Columns WHERE name = 'Id' ) AND Tables.Name LIKE 'Table%' ) a INNER JOIN Cte ON a.RowNum = Cte.RowNum + 1 ) SELECT Substring(Name, CharIndex('SELECT', Name), 9999999) FROM Cte WHERE Cte.RowNum = (SELECT Max(RowNum) FROM Cte); Exec(@MyQuery);b471code3
Star
13877 Points
2598 Posts
Re: Use like or '% for query object database
Aug 20, 2012 06:04 PM|LINK
I was thinking of that as well Matts...only downfall is getting the table names. If there are 1000+ tables my guess is there probably isn't a determining factor to tell which tables the OP needs to get. Maybe if there was a naming convention that could be queryable like Products.ProductDesc, Products.Product, Product.ProductImages, etc. you could search for all Products.% schema. Looking for a column name of "ID" could return unexpected results, not saying it couldn't work, just probably need additional verification you are getting the correct tables.
Or if there were a property you could set on the table and query that but then again I don't know if that can even be done.