views:

39

answers:

1

I tried to execute scripts from [1] in model database and user-defined databases but they give definitions/scripts of only user-defined (non-system) views, i.e. those that I anyway can easily get from GUI.

How can I see/script the definition/script of a system view in SQL Server 2008 R2?

[1]
Answers to question "System Views text in SQL Server 2005"
http://stackoverflow.com/questions/432500/system-views-text-in-sql-server-2005

+2  A: 
select object_definition(object_id('[sys].[server_permissions]')) 
      AS [processing-instruction(x)] FOR XML PATH('') 

(The XML bit is just to prevent long definitions getting truncated when viewed in SSMS)

You can also stop the SQL Server instance. Copy the mssqlsystemresource file (on my system this is at C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\mssqlsystemresource.mdf and then re-attatch the copy under a new name).

In the reattached version it is easier to poke around and see the various definitions using normal SSMS functionality.

Martin Smith
@Martin Smith, thanks for answer! Please see my follow-up questions http://stackoverflow.com/questions/3877923/questions-on-copying-nonsystem-nonused-sql-server-database
vgv8