for views: get Current connection parameters > User and Description > Schema from 2005 system

[mehlsl]

Hello --

Using 2005 Object Explorer, when I right-click on a view > Properties
the right frame shows the 3 sub-frames:
Current connection parameters, Description, and Options

I want to query the system tables to get information on the database's views, including the following two values from 2 of those 3 sub-frames:
Current connection parameters > User
and
Description > Schema

Can someone tell me which 2005 system tables contain these values?

Thanks in advance,

Stephen Mehl

 

[gmmastros]

The user part is pretty easy...

Select suser_sname()

Description > Schema I assume to mean the schema that the view was created with. If so, take a look at this:

select schema_name(schema_id), * from sys.views

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

Understand that the user information has nothing to do with the view. It's showing who is currently connected to the instance within Management Studio. IOW it will always show your login, not anyone else's.

-----------
With business clients like mine, you'd be better off herding cats.

 

[mehlsl]

George and philhege --

Thank you.

I think George's second suggestion is what I need if my assumption that
Description > Schema
is the user that created the view. Am I correct?

If this is not correct, do you or does anyone know where to find the creator and modifier of the view, even if they created it as "dbo"?

Thanks again for any help,

Stephen

 

[philhege]

Depending on the timing, you can look at SQL 2005's default trace log. It should reveal the login of the entity that created or changed the object.

http://social.msdn.microsoft.com/Fo...190ac/find-creator-of-a-table-sql-server-2005

http://www.mssqltips.com/sqlservertip/1111/default-trace-in-sql-server-2005/

-----------
With business clients like mine, you'd be better off herding cats.