SELECT from all tables in the database
-
I can't help you there, I don't know how to pull the table name up as a column. It seems like if you have a bunch of identically structured tables and you wanted a query of this type most of the time, you might be better off combining everything into one big table with an extra column "Tablename" or whatever. Just my 2 cents.
-Aaron
-
I can help with that. On SQL2k run as is (change @DB to @DB if SQL7)
declare @Query varchar(1000),
@FullQuery varchar(8000)
Set@Query = 'Select * From Table1'--Put Query here
Declare @DB Table (Query varchar(1000))
Insert @DB
select replace(replace(@Query, 'Select', 'Select ''' + Catalog_Name + ''','), 'From ', 'From dbo.' + Catalog_Name + '.')
from INFORMATION_SCHEMA.Schemata
Where Catalog_Name not in ('msdb', 'tempdb')--Add other exceptions
select @FullQuery = isnull(@FullQuery + ' Union All ' + char(10), '') + Query from @DB
print (@FullQuery)--Change to "Exec" if you want dataset
Signature is NULL
-
This is a great thread, and there are some great solutions, but it all begs a question about structure: Why do you have 20 de-normalized tables that each contain the same column schema? I think that containing data in your table name violates at least one of the forms of normalization. If the app requires there to be seperate information, it should be more efficient and easier to maintain if the tables were combined and the metadata represented by the tablename was put into a new column. After that, you could access the data by Stored proc, dynamic query, or even 20 seperate views, if the app requires it. I am in the middle of trying to do a similar task to a database that is horribly denormalized, and I have found that it helps both performance and maintenance to re-normalize. At least in my case...
-
create table #temp (tablename varchar(250),[col_name] varchar(250),[col_date] datetime)
exec sp_MSforeachtable @command1 = "insert into #temp select '?',[col_name],[col_date] from ? where datepart(year,[col_date]) = 2003"
select * from #temp
drop table #temp
Far away is close at hand in the images of elsewhere.
Anon. -
Not that the solutions won't work, but I would be careful to implement undocumented features of SQL Server like sp_MSforeachtable in production code.
I would use them only for adminstration purposes
Just my $0.02
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Is there in specific reason Frank, why we should not be using "undocumented" stored procedures in a production environment? Just curious.
SQLBill: I tried setting QUOTED_IDENTIFIER on and off, but it still gives me the error.
thanks,
v
-
quote:
Is there in specific reason Frank, why we should not be using "undocumented" stored procedures in a production environment? Just curious.yes, it's like directly querying system tables.
Microsoft claims that they have the right to change these things on a Service Pack level, I think.
However, they made a step back. In SQL7 they strictly said don't use, in SQL2K they say don't use what isn't documented (and that's not much of those undocumented things).
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
------------------------------------------
Quote:
Not that the solutions won't work, but I would be careful to implement undocumented features of SQL Server like sp_MSforeachtable in production code.
I would use them only for adminstration purposes
------------------------------------------
NOT only that, but if you actually look in the master db and check out the code for "sp_MSforeachdb" you'll see exactly why it is "undocumented". It references the sysdatabases table directly, which may or may not change with new versions of SQL; AND
It's using a dynamically generated cursor, combining two of the least efficient SQL techniques.
Use the INFORMATION_SCHEMA views for querying metadata; they are documented and will continue to be supported. For example
select
Catalog_Name
from INFORMATION_SCHEMA.Schemata
From there it's trivial to build a "union all" query string that will return a dataset (see post above).
Signature is NULL
-
You can get the tables names like this.
select name as 'TableName' from sysobjects where type = 'u'
dump them into a temp table and use them to dynamically create a select statement from each table. Since you are dynamically creating the select statement you can also include a select tablename = TableName, col1 from tablename..... just as push in the right direction anyway.
I am with SCPORICH.....why 20 tables with same schema?
-
-----------------------------------
Quote:
select name as 'TableName' from sysobjects where type = 'u'
------------------------------------
Like Frank said, though...querying system tables directly is risky, as they can change without warning.
select
Catalog_Name
from INFORMATION_SCHEMA.Schemata
Gives you the same results, and it's supported:
-------------------------------------
-------------------------------------
declare @Query varchar(1000),
@FullQuery varchar(8000)
--Query Set Here
Set @Query = 'Select * From Table1'--Put Query here
Declare @DB Table (Query varchar(1000))
--Creates one select for each db with the DB Name as the first column
Insert @DB
select replace(replace(@Query, 'Select', 'Select ''' + Catalog_Name + ''','), 'From ', 'From dbo.' + Catalog_Name + '.')
from INFORMATION_SCHEMA.Schemata
Where Catalog_Name not in ('msdb', 'tempdb')--Add other exceptions
--Creates a "Union All" Query that includes each row in @DB
select @FullQuery = isnull(@FullQuery + ' Union All ' + char(10), '') + Query from @DB
print (@FullQuery)--Change to "Exec" if you want dataset
Signature is NULL
-
I just got hold of this database, and as SCPORICH said I need to re-normalize all these tables too. But till then I needed a work around 🙂
v
-
When I run
select Catalog_Name
from INFORMATION_SCHEMA.Schemata
alls it returns is DATABASE container names.....am I missing something here?
-
Uh...Oh yeah. I think that's what this conversation was about...running a query for each database, not for each table.
For Table Names you should use:
select Table_Name
From INFORMATION_SCHEMA.Tables
The rest of the code loops through the table names and creates a text variable that includes the query for each db (with a "union all" between them.
I wrote a lot of code directly against the system tables, then realized that this wasn't necessarily good practice. I've since started using the INFORMATION_SCHEMA views, as they're supported.
Although I have yet to find a view that does this:
selectso1.Name as FKConstraint, so.Name as FromTable,
sc.Name as FromColumn, so2.Name as ToTable, sc1.Name as ToColumn
From sysobjects so (nolock)
JOINsysforeignkeys fk (nolock) on so.[id] = fk.fkeyid
joinsyscolumns sc (nolock) on fk.fkeyid = sc.id and fk.fkey = sc.colid
JOINsysobjects so2 (nolock) on fk.rkeyid = so2.id
joinsyscolumns sc1 (nolock) on fk.rkeyid = sc1.id and fk.rkey = sc1.colid
JOINsysobjects so1 (nolock) on fk.constid = so1.id
whereso.name = <Table Name>
Signature is NULL
-
Why not use a partitioned view on the
tables ?
-
Thanks to all who contributed to this thread and especially Calvin. Your ideas helped me in a pinch to get out some needed reports in short order, saving me much time and energy. Thanks so much!
Paul
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply