Mgmt Studio: List versus Details view

So now all the Details views have the "Policy Health State" column. Great. But when will we be able to add other columns to the details views? Or at least put back what SQL 2K used to show in details views? Pretty please?

Hi Bob,

We actually have plans to only display the Policy Health State in the Details section for only those objects that have policy state. For CTP6, we are looking to extend the Object Details to show many more properties than you had in the past. Please stay tuned.

Bill Ramos,

Lead PM

|||Many, many thanks Bill.

Metadata Inheritance

Hello,
I have written a mechanism which returns meta data back to a client for
different views. For example, one meta data item on a column is the Display
Name.
Now, so I don't have to keep attaching meta data to a particular column
every time I create a new view, I "inherit" the meta data from a parent view
or table by looking at all the same named columns in any views or tables
that the table depends (sysdepends) on.
The problem is, sometimes it make sense to change the name of a column in a
view from the underlying table (e.g. company table has a "name" column, but
in the view, you want to call it "company_name").
My question is, is there an easy way to determine where a view's column
really came from?
Thanks,
-PaulNever mind, I forgot to look at the depnumber column in sysdepends!
"Paul" <a@.b.com> wrote in message
news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have written a mechanism which returns meta data back to a client for
> different views. For example, one meta data item on a column is the
> Display Name.
> Now, so I don't have to keep attaching meta data to a particular column
> every time I create a new view, I "inherit" the meta data from a parent
> view or table by looking at all the same named columns in any views or
> tables that the table depends (sysdepends) on.
> The problem is, sometimes it make sense to change the name of a column in
> a view from the underlying table (e.g. company table has a "name" column,
> but in the view, you want to call it "company_name").
> My question is, is there an easy way to determine where a view's column
> really came from?
> Thanks,
> -Paul
>|||Okay, now the hard question.
How do I correlate a column in the sysdepends table back to a column in the
resulting view's columns?
Thanks,
-Paul
"Paul" <a@.b.com> wrote in message
news:OBXyfyVCFHA.1836@.tk2msftngp13.phx.gbl...
> Never mind, I forgot to look at the depnumber column in sysdepends!
>
> "Paul" <a@.b.com> wrote in message
> news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||On Wed, 2 Feb 2005 15:27:44 -0500, Paul wrote:

>Okay, now the hard question.
>How do I correlate a column in the sysdepends table back to a column in the
>resulting view's columns?
Hi Paul,
You don't.
You are probably thinking right now of fairly straight-forward views,
where such a mapping would be viable. But views can be fairly complex!
CREATE VIEW test
AS SELECT Col1 - Col2 AS A,
Col1 + Col2 AS B,
CASE WHEN EXISTS (SELECT *
FROM YZ
WHERE Foo = SomeTable.Bar)
THEN 1
ELSE 2
END AS C,
'Constant value' AS D,
Col3 + 1 AS E,
Col3 - 1 AS F
FROM SomeTable
How should each of the view's columns be correlated to each of the
underlying table's columns? And remember that this is also a fairly simple
example - I could come up with much more contrived views (some of my real
views for projects I do are much more contrived!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Metadata - Order by Information

Hey there,
Im wondering if there is a way to determine which views in my database use the "order by" statement.

The reason I need this is because we need to migrate over to MS SQL 2005 where the order by statements are ignored within the views themselves. Now(in mssql 2005) you need to explicityly state the order by now when calling a view

ie. select * from [viewname] order by column x, y desc, z
instead of

ie. select * from [viewname]
where the view already had the applicable sorting done within the view.

If those order by statements are ignored, some production software which rely on the ordered data will corrupt.

Please let me know if there's a way to query the actual database and determine which views have 'order by' statements in them.

thxi dont think you can have order by clause in VIEW.|||you can in MS SQL 2000 but not in MS SQL 2005|||My SQL2K did complain order by in view, it seems strange. Anyway you can try to search for "order by" in syscomments if your views are not encrypted.|||you can use this script to list the view names having order by clause

select name from dbo.sysobjects where objectproperty(id, N'IsView') = 1 and
id in (select id from dbo.syscomments where text like '%order by%')|||you can use this script to list the view names having order by clauseYou need to put a BIG caveat on this idea... While it works for views with nicely formatted source code, it can generate both false positives (for any view with either a commented ORDER BY clause, or the text ORDER BY), and false negatives (where the words order and by have something other than a space between them, such as a line-end, tab, multiple spaces, comments, etc).

The only way to be truly certain is to actually parse the view and check the tokenized output for an ORDER BY. This is beyond the reach of simple Transact-SQL, although I'm thinking about a couple of ways that could let you work around this limitation.

-PatP|||Thx for all the responses. I will try the following:

select name from dbo.sysobjects where objectproperty(id, N'IsView') = 1 and
id in (select id from dbo.syscomments where text like '%order by%')
as suggested by kadapa22. Although it may result in both false postives and negatives, I think it's worth a try. Better than manually looking through over 800 views manually. I will let you know how it goes.

thx again.|||Thx for all your advice. I decided to take a different approach to make sure I get the proper results. I exported all my views to a text file and will search through that instead.

thx again for all your help.