sql - SQL .How to：从以 data.PIR 开头的表中选择 *

Question

想象一下，我有多个 sql 表，它们被命名为：

data.PIR_12
data.PIR_13
data.Pir_2455
data.Pir_somedata

我可以做一些选择吗

select * from data.PIR% where code = 'mycode'

还是因为架构可能不同而不可能？

标签： sqlsql-server

Answer 1

I would second the comments that say "please fix your data structure". Please look up data normalisation & have a long hard think before implementing something like this.

Only when you get to already be huge scale might you need something approaching data buckets..

If you think you need this because you have varying data content, then go back to normalisation and consider abstracting further. You can get as far abstract as (name, value)

Still, if you know the structure and you are sure that dynamic table names are the answer, then in sqlserver you could construct dynamic sql.

declare @sql nvarchar(2000), @sometable nvarchar(50), @myid nvarchar(10)
set @sometable='_12'
set @myid = '12'
set @sql = 'select name from data.pir' + @sometable + ' where value='+@myid
exec @sql

but you are probably into lots of trouble already - these queries are hard to handle the results from or to wrap error handling around.

You can select from the metadata views to get your list of tables. Or put the table list in a table of your own design. And write an iterator to go through the metadata output with your dynamic sql.

there are cases for dynamic sql, but I would suggest long since outmoded (they used to be aimed at dynamic where clauses for complex ad-hoc queries).

In the past I have used these queries for making very large numbers of cross-server callss (the same sp is invoked, only varying the servername (& there are timing reasons to serialise all the calls making loop iteration the most effective approach)