Comma delimited list of views

Is it possible to get a comma delimited list of the views in a DB?

What type of DB? Sql Server? Access? MySQL?|||

Add a reference to the Smo namespace, this will give you access to some very powerful classes for manipulating sql server. I know you can obtain a reference to a collection of all the views in a given database. Then simply do:

StringBuilder commaViews = new StringBuilder();

foreach (View vw in myDatabase.Views)
{
commaViews.Append(vw.Name + ", ")
}

|||

Jack,

Bluemistonline has a nice programmatic answer. But your question isn't clear on whether you want this for coding purposes or you just want a straight list from the database. If this is for a DBA query then see below, otheriwse the previous reply is a good answer.

selectTable_Name +','fromInformation_Schema.TableswhereTable_Type ='View'

|||

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

|||

HiJackxxx,

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

What Diamsorn suggested is almost correct. The only thing you need to do is to initialize the @.s string(or it will be recognized as NULL) .

plus, Diamsorn, why do we need to use the COALESCE function? I cannot see the reason why we need to do that.

like this:

Declare @.sAs VarChar(4000)

Set @.s=''Select @.s =@.s + Table_Name+','fromInformation_Schema.TableswhereTable_Type ='View'

Hope my suggestion helps

|||

Bo Chen – MSFT:

HiJackxxx,

Do this in a stored proc. This will give you a single row of table_name that is comma delimeted.

Declare @.sAs VarChar(4000)Select @.s =COALESCE(@.s +',','') + Table_NamefromInformation_Schema.TableswhereTable_Type ='View'

What Diamsorn suggested is almost correct. The only thing you need to do is to initialize the @.s string(or it will be recognized as NULL) .

plus, Diamsorn, why do we need to use the COALESCE function? I cannot see the reason why we need to do that.

like this:

Declare @.sAs VarChar(4000)

Set @.s=''Select @.s =@.s + Table_Name+','fromInformation_Schema.TableswhereTable_Type ='View'

Hope my suggestion helps

Initializing the variable is always a good idea, but its not necessary in this case. Adding a varchar value to a null still gives you the varchar value. However when using COALESCE if we do initialize the varialbe 1st we will have a ',' for the start because that COALESCE was not able to handle the NULL value of @.s

The reason for using COALESCE is because it will handle null values. In the case of this example you wont have a null value coming from your information_schema. But take an example where someone was wanting a comma delimited list of say zipcodes, or another type of column that does have null values. COALESCE will handle the nulls for us. And we dont have to remove the trailing ','

|||

thanks :)

I tested your code and mine in sql and they both work fine.