Mike,
Thanks!


-----Original Message-----
From: Michael_Beadles
[mailto:mssqldba-ezmlmshield-x73406692.[Email address protected]
Sent: Thursday, October 27, 2005 10:20 AM
To: LazyDBA Discussion
Subject: Re: SQL Server 2005 System Tables


Some examples from that free Microsoft eCourse(SQL 2005 Monitoring and

Management) I downloaded ...





Catalog Views



List Databases



This code lists the databases on the server.

Note that this view returns the same results regardless of current database

context.



USE AdventureWorksSELECT * FROM sys.databases





List Tables



This code lists the tables in the current database.



USE AdventureWorksSELECT * FROM sys.tables





List Columns from all Database Tables



This code lists the columns from all the tables in the current database.



USE AdventureWorksSELECT * FROM sys.columns





List Columns in a Single Table



This code lists the columns in the Person.Contact table.



USE AdventureWorksSELECT * FROM sys.columns

WHERE [object_id] = (SELECT [object_id] FROM sys.tables ST

JOIN sys.schemas SS

ON ST.schema_id = SS.schema_id

WHERE ST.name='Contact' AND SS.name = 'Person')





List Registered Assemblies



This code lists the registered assemblies in the database.



USE AdventureWorksSELECT * FROM sys.assemblies







List Registered Database Events



This code lists the registered events in the database.



USE AdventureWorksSELECT * FROM sys.events





List Server Configuration Options



This code lists the server configuration options. Note that this view

returns the same results regardless of current database context.



USE AdventureWorksSELECT * FROM sys.configurations







Dynamic Management Views



List Table Partitions



This code lists the table partitions in the database.



USE AdventureWorksSELECT * FROM sys.dm_db_partition_stats



Transaction Locks



USE AdventureWorks

-- list the transaction locks currently held in the database

SELECT * FROM sys.dm_tran_locks

-- execute a query without releasing locks and then list the locks.

-- Note that there are many more locks than previously listed

BEGIN TRANSELECT * FROM Person.Contact WITH (HOLDLOCK)SELECT * FROM

sys.dm_tran_locks

-- commit the transaction, thus releasing the locks.

-- Note that the list of locks has returned to the original value.

COMMIT TRANSELECT * FROM sys.dm_tran_locks



458752

7

1

0

6

âEUR¦



524288

8

0

1

11

âEUR¦













"Edwards Ed "

<mssqldba-ezmlmsh

ield-x13199611.x1 To

[Email Address Removed] "LazyDBA Discussion"

.com> <[Email address protected]

cc

10/27/2005 05:52

AM Subject

SQL Server 2005 System Tables





















To All,

Is it true that the system tables no longer exist in SQL Server 2005 but

these same system tables are listed under "Views". If so, what are their

qualifiers (EX. dbo)?









---------------------------------------------------------------------

TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY

Website : http://www.LazyDBA.com

To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------

TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY

Website : http://www.LazyDBA.com

To unsubscribe: http://www.lazydba.com/unsubscribe.html







---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page