Joining 2 TSQL statements - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Joining 2 TSQL statements

Author

Topic

RajJol
Starting Member

17 Posts

Posted - 2010-10-08 : 10:08:17

Hi People,

I need help with joining two SQL statements I have produced. Basically I have a number of tables in a database and only want to check the tables which have a column called "insertdate". I currently have a statement which provides me with results how I want them, however, I have to specify the tables it checks. I do not want to do this and would like a loop to check each table and only refer to the tables with an "insertdate".

Below is my current loop and a second statement which returns all the tables in my database which has the column "inserdate". How can I merge these two together to get my desired results or how can I modify?

STATEMENT 1

declare @sql varchar(max),@search varchar(100)
set @sql=''
set @search='insertdate'
select
@sql=@sql+'select
case
when left('''+t.table_name+''',1)=''A'' then ''Attack''
when left('''+t.table_name+''',1)=''D'' then ''Defend''
when left('''+t.table_name+''',1)=''M'' then ''Midfield''
else ''Other''
end as Source,
'''+ t.TABLE_NAME+''' ''TableName'', CONVERT(CHAR(10), InsertDate, 120) ''InsertDate'', DATEDIFF (d,InsertDate, GETDATE()) ''DaysOld'', COUNT(*) ''Count'' FROM'+' ['+t.TABLE_NAME+']'+' GROUP BY CONVERT(CHAR(10), InsertDate, 120), DATEDIFF (d,InsertDate, GETDATE()) union all '
FROM information_schema.tables as t
where
t.table_type='BASE TABLE' and t.TABLE_NAME in ('A_Players','Global_Players','M_Players_Subs') --data_type like '%char%'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)

STATEMENT 2

select O.name objectName, C.name ColumnName
from sys.columns C inner join sys.objects O
ON C.object_id=O.object_id
where C.name like 'insertdate'
order by O.name,C.name

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 11:30:07

[code]declare @sql varchar(max),@search varchar(100)
set @sql=''

-- USE THIS VARIABLE TO DEFINE THE NAME OF THE SEARCHED DATECOL!!
set @search='insertdate'

select @sql=@sql+
'select
case
when left('''+t.table_name+''',1)=''A'' then ''Attack''
when left('''+t.table_name+''',1)=''D'' then ''Defend''
when left('''+t.table_name+''',1)=''M'' then ''Midfield''
else ''Other''
end as Source,
'''+ t.TABLE_NAME+''' ''TableName'', CONVERT(CHAR(10), '+@search+', 120) '''+@search+''', DATEDIFF (d,'+@search+', GETDATE()) ''DaysOld'', COUNT(*) ''Count'' FROM'+' ['+t.TABLE_NAME+']'+' GROUP BY CONVERT(CHAR(10), '+@search+', 120), DATEDIFF (d,'+@search+', GETDATE()) union all '

--select *
FROM information_schema.tables as t
where
t.table_type='BASE TABLE' and t.TABLE_NAME in
(select O.name
from sys.columns C inner join sys.objects O
ON C.object_id=O.object_id
where C.name = @search)
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources