CANT USE A VARIABLE INSTEAD OF TABLE NAME (eg From @var)

Hi every1,

I’ve got a DB with roughly 90 tables, roughly half of these tables have a column called “last_date”. I want to find find the last “last_date” in each table that has this coulmn but only the tables that contain it. I have tried using which does return all the “last_date” but as checks ALL TABLES (not just the ones with the “last_date” column) it gives me an error for all the table it checks which doesn’t have that column

(I KNOW ITS LONG ....GO ON JUST A BIT LONGER )

Have also tried cursors using “ select object_name(id) from syscolumns where patindex('LAST_DATE',name) <> 0 ” which returns a list of all the tables with “last_date” in it. This won’t work as I cant use a variable (@cur_tab_name ) instead of the table name (def 1 of the most annoying things about SQL) “select max(LAST_DATE) from @cur_tab_name”


ANY HELP WOULD BE REALLY APPRECIATED
Thanks,
tjbyrne

Here is some sample code that aggregates data from several tables with the same structure into one table. A previous comment suggested that you should never need to do anything like this. However, in the real world where everything does not fit nicely into a box as in this example, it is necessary to find a solution like this. Let's all try to be a little more open and willing to solve problems rather than saying they should not exist.

I hope this helps.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempBatch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TempBatch]
GO

CREATE TABLE [dbo].[TempBatch] (
[batchnum] [int] NOT NULL ,
[seq] [int] NOT NULL ,
[ssn] [varchar] (11) NOT NULL ,
[enr_type_code] [int] NOT NULL ,
[check_num] [varchar] (10) NULL ,
[bank_name] [varchar] (30) NULL ,
[check_amt] [money] NULL ,
[cc_num] [varchar] (30) NULL ,
[exp_date] [varchar] (5) NULL ,
[date_verified] [datetime] NULL ,
[app_flag] [varchar] (1) NULL ,
[app_code] [varchar] (10) NULL ,
[cc_type_code] [int] NOT NULL ,
[lsn_id] [int] NOT NULL
) ON [PRIMARY]
GO


SET NOCOUNT ON
DECLARE @table_name varchar(40)
DECLARE tables_cursor CURSOR FOR
select name from sysobjects
where type = 'U' and name like 'CCC%'
order by name

OPEN tables_cursor

-- Perform the first fetch.
FETCH NEXT FROM tables_cursor INTO @table_name

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC('Insert TempBatch (batchnum,seq,ssn,enr_type_code,check_num, bank_name,check_amt,cc_num,exp_date,date_verified,app_flag,app_code,cc_type_code,lsn_id) Select batchnum,seq,ssn,enr_type_code,check_num, bank_name,check_amt,cc_num,exp_date,date_verified,app_flag,app_code,cc_type_code,lsn_id FROM ' + @table_name)

FETCH NEXT FROM tables_cursor INTO @table_name

END

CLOSE tables_cursor
DEALLOCATE tables_cursor
GO

Hello rongower
u r idea really helped me a lot .
Once again thanks a lot .

Sachin