Merge/Add/Concatenate different select statements

Question

Merge/Add/Concatenate different select statements

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 25, 2010 at 7:34 pm

#239478

Mentioned below is my sql. @server and @database are created dynamically. I need to get name,type_desc,create_date,modify_date,@server and @database columns in one table. Any suggestions?
select name,type_desc,create_date,modify_date from [' + @server + '].[' + @database + '].sys.views
where create_date > ''2010-08-24''
order by modify_date desc

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

iqtedar (8/25/2010)
Mentioned below is my sql. @server and @database are created dynamically. I need to get name,type_desc,create_date,modify_date,@server and @database columns in one table. Any suggestions?
select name,type_desc,create_date,modify_date from [' + @server + '].[' + @database + '].sys.views
where create_date > ''2010-08-24''
order by modify_date desc

declare @sql_cmd varchar(max);

set @sql_cmd = 'select name,type_desc,create_date,modify_date from [''' + @server + '''].[''' + @database + '''].sys.views

where create_date > ''2010-08-24''

order by modify_date desc'

exec (@sql_cmd);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

thanks for the reply. Not sure whats the difference between yours and mine . Your doesn't work. Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 3

I see I forgot to comment what I was doing.

Since the server/db are in variables, you need to build a string to put the variables in. Then execute the string.

And, I see that the quotes inside the brackets shouldn't be there.

So, this is working for me:

declare @sql_cmd varchar(max);

set @sql_cmd = 'select name,type_desc,create_date,modify_date from [' + @server + '].[' + @database + '].sys.views

where create_date > ''2010-08-24''

order by modify_date desc'

print @sql_cmd

exec (@sql_cmd);

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

curious_sqldba

SSC-Dedicated

Points: 36494

August 27, 2010 at 1:36 pm

#1213218

thanks