i have a set of tables that are exactly the same but changing the first value only, its a date value like this:
dbo.[2016_09_06_CHEQ]
so instead of writing the same query but changing the date in the name is there a way to search all over the tables?? something like this?
dbo.[*_CHEQUES]??
is there a way to do this? or how can i archive something like this on my statement
Another option is to do it with a cursor:
DECLARE @tableName VARCHAR(50)
DECLARE @query NVARCHAR(MAX) = ''
DECLARE db_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%_CHEQUES'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = @query + 'SELECT CommonField1, CommonField2 FROM ' + @tableName + ' WHERE CommonField3 = value '
FETCH NEXT FROM db_cursor INTO @tableName
IF @@FETCH_STATUS = 0
-- SET @query = @query + CHAR(13) + CHAR(10) + 'UNION ALL ' + CHAR(13) + CHAR(10) -- To add line breaks for easier reading when debugging
SET @query = @query + 'UNION ALL '
END
CLOSE db_cursor
DEALLOCATE db_cursor
--PRINT @query -- To debug the result query
EXEC sp_executesql @query
Maybe something like:
declare @q nvarchar(MAX) = 'SELECT * FROM '
;with cte as (
SELECT TABLE_NAME
FROM information_schema.tables
WHERE TABLE_TYPE='BASE TABLE'
AND table_name like '%_CHEQ'
)
select @q = @q + '[' + table_name + '] UNION SELECT * FROM ' FROM cte
SET @q = SUBSTRING(@q, 1, LEN(@q) - 19)
EXEC sp_executesql @q
*), I would initialize @q = '' and then do select @q = @q + ' UNION ALL SELECT * FROM [' + table_name + ']' FROM cte and SET @q = SUBSTRING(@q, 12, LEN(@q)).I am assuming that you are using SQL Server. You can use the undocumented procedure sp_MSforeachtable with this sys stored procedure you can retrieve all information from your tables using a single statement
USE YOUR_DATABASE GO EXEC master.sys.sp_MSforeachtable @command1="SELECT * FROM ?"
This statement runs a SELECT in each table present in your database. Also you can use a Dynamic Query
DECLARE @name varchar(400),
@sql varchar(4000)
SELECT @name = (SELECT TOP 1(name) FROM sys.objects WHERE type = 'U' and name like '%CHEQ%' ORDER BY name)
WHILE @name IS NOT NULL
BEGIN
SELECT @sql = 'SELECT * FROM [dbo].['+@name+']'
EXEC(@sql)
PRINT 'TABLE PROCESSED: '+@name
SELECT @name = (SELECT TOP 1(name) FROM sys.objects WHERE type = 'U' AND name > @name ORDER BY name)
END
GO
like in the last SELECT. One downside of this approach is that you are doing multiple queries instead of one to get all the tables and one to query them all. In this case it's harmless, but bear in mind that EXEC is prone to SQL injection, so never use it with use input.