SELECT from multiple tables in a DB
-
Is there a way to SELECT from all tables of a specific "Type" in SQL Server 2000? For instance, I have abotu 50 tables that are of "User" type that I want to query, and I am not sure of the most efficient way. If it helps, this is the information I am wanting from ALL tables because they all contain these columns: SELECT submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status FROM WHERE status = 'OPEN' Can someone tell me the best way? Thanks...Robby
-
Is there a way to SELECT from all tables of a specific "Type" in SQL Server 2000? For instance, I have abotu 50 tables that are of "User" type that I want to query, and I am not sure of the most efficient way. If it helps, this is the information I am wanting from ALL tables because they all contain these columns: SELECT submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status FROM WHERE status = 'OPEN' Can someone tell me the best way? Thanks...Robby
-
You can start with this: sp_MSforeachtable 'select * from ?' and then customize the select with whatever you want.
This is a great command. I can't seem to get it to do what I want. I have the command, but it seems to be just "picking" a table out of the database and displaying the properties. I am attempting to display this information in an HTML table. Here's the code: sqlstr="sp_MSforeachtable 'select submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status from ?'" Set results = MyConn.Execute(sqlstr) submitDate=results("submitDate") requestorLastName=results("requestorLastName") and so forth for each column... I have approx 50 tables, and my html output only displays entries for only one of the tables...what do I need to do to be able to query all and display all for whatever table matches the query? Thanks
-
This is a great command. I can't seem to get it to do what I want. I have the command, but it seems to be just "picking" a table out of the database and displaying the properties. I am attempting to display this information in an HTML table. Here's the code: sqlstr="sp_MSforeachtable 'select submitDate, requestorLastName, requestorFirstName, urlToForm, nof, status from ?'" Set results = MyConn.Execute(sqlstr) submitDate=results("submitDate") requestorLastName=results("requestorLastName") and so forth for each column... I have approx 50 tables, and my html output only displays entries for only one of the tables...what do I need to do to be able to query all and display all for whatever table matches the query? Thanks
This is because you're getting multiple recordsets. When you reach the end of the first recordset, use the NextRecordset method of the recordset to get the next table. Concussus surgo. When struck I rise.