need help with question - 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)

need help with question

Author

Topic

adiboy0420
Starting Member

10 Posts

Posted - 2012-07-16 : 23:52:38

please help

i have a query so far is below, i am later going to turn this into a fuction and have a input parameter fromt he user with either as T or V.

my code is :

declare @x varchar (10)

set @x = 'V'

if @x='V'

begin --1

select name
from sys.views
where is_ms_shipped = 0
end --1
else
Begin --2
if @x = 'T'

begin--3
select name

from sys.tables
where is_ms_shipped = 0
end--3

end

-- which works fine and gives me back

vAdditionalContactInfo
vEmployee
vEmployeeDepartment
vEmployeeDepartmentHistory
vIndividualCustomer
vIndividualDemographics
vJobCandidate
etc
ect

but i need the answer to be

vAdditionalContactInfo;vEmployee;vEmployeeDepartment;vIndividualCustomer;vIndividualDemographics;vJobCandidate;ect;ect

--------------------- question-------

so i am trying to build a table line fuction, so how do i do that??

if you can help me with the coding part i can just work on creating it as a table line function.... please give me clues .....

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-17 : 02:09:52

declare @x varchar (10)

set @x = 'V'

if @x='V'

begin --1

select SUBSTRING(( SELECT '; ' + name
from sys.views
where is_ms_shipped = 0
FOR
XML PATH('')
), 2, 8000) AS view_name
end --1
else
Begin --2
if @x = 'T'

begin--3

select SUBSTRING(( SELECT '; ' + name
from sys.tables
where is_ms_shipped = 0
FOR
XML PATH('')
), 2, 8000) AS table_name

end--3

end
------------

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 10:22:07

isnt it enough to use INFORMATION_SCHEMA.TABLES which contains both tables and views

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-18 : 05:19:49

visakh you are just a genious ;)

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 10:32:42

quote:
Originally posted by lionofdezert

visakh you are just a genious ;)

--------------------------
http://connectsql.blogspot.com/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources