Select data based on table name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select data based on table name

Author

Topic

lancepr
Starting Member

17 Posts

Posted - 2003-04-22 : 15:18:35

Hi all,

I have done a similar task in asp and have been trying to get it to work is QA.

I want to select all tables fromt he sysobjects that meet a condition, example:

 
select * from sysobjects
where name like '%_customers'

Say we have about 50 tables that end in _customers.
Now I want to be able to select their names and address from those tables without having to hard code the tables names in with union.

What would be the best way to do this? In asp its easy just drop the sysobjects into an array and then loop throught the array but SQL is newer to me and giving me troubles.

thanks,
Lance

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-22 : 15:40:57

OK, I would highly reccommend creating a view (partitioned or not). However because I like to play with this type of stuff (there's nothing you can't do with the catalog). Check out the code below.

The code will execute if you cut and paste it. However for your purposes I would change 2 things. You need to create an IN List for the columns phrase, or if you know each and every column just modify the first SELECT and remove the second...probably easier and 2 change the GO phrase to a UNION ALL.

Anayway GOOD LUCK (please go with the view):


USE NorthWind
GO

DECLARE @TBName sysname, @SQL varchar(8000)


SELECT @TBName = 'Cust', @SQL = ''

SELECT @SQL = @SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT  ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE @TBName+'%'
   AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE @TBName+'%'
   AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE @TBName+'%'
   AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME LIKE @TBName+'%'
   AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @SQL

EXEC(@SQL)

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 16:04:33

Lance --

Just out of curiousity, why do you have 50 tables that end in customer? Why are these not all in the same table?

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 20:11:49

Just a guess

Separation of Data
Horizontal Partioning

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-04-23 : 16:01:27

You may do something like this -

---------------------------------
declare @EXECSQL nvarchar(500)
declare @tbName nvarchar(50)

declare cursor cr_Cust for
select name from sysobjects
where type = 'u' and name like '%_customers'
open cr_Cust
fetch next from cr_Cust into @tbName
while ( @@fetch_status = 0 )
begin
select @EXECSQL = N'select name, address from ' + @tbName
execute sp_executesql @EXECSQL
fetch next from cr_Cust into @tbName
end
deallocate cr_Cust
--------------------------

Hopefully it's what you look for.

George

quote:

Hi all,

I have done a similar task in asp and have been trying to get it to work is QA.

I want to select all tables fromt he sysobjects that meet a condition, example:

select * from sysobjects
where name like '%_customers'

Say we have about 50 tables that end in _customers.
Now I want to be able to select their names and address from those tables without having to hard code the tables names in with union.

What would be the best way to do this? In asp its easy just drop the sysobjects into an array and then loop throught the array but SQL is newer to me and giving me troubles.

thanks,
Lance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources