The sys.objects catalog view has a row for every schema-scoped object, so you can retrieve any user defined object details, like foreign keys, by object type.
SQL SERVER – 2005 – Retrieve Processes Using Specified Database
Jim Sz shared a quick script to retrieve processes that use a specific database from sys.sysprocesses, with spid, status, host name and login.
SQL SERVER – Get a Row Per File of a Database as Stored in the Master Database
Each database has at least two files. This quick script on sys.master_files returns a row per file of a database, just as it is stored in master.
SQL SERVER – Reclaim Space After Dropping Variable – Length Columns Using DBCC CLEANTABLE
DBCC CLEANTABLE can reclaim selected space left after dropping variable-length columns. I distinguish that table operation from shrinking a database file.
SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
How do you find Fragmentation Information for a table in SQL Server 2005? DBCC SHOWCONTIG has limits, so I use sys.dm_db_index_physical_stats instead.
SQL SERVER – 2005 – Last Ran Query – Recently Ran Query
Want to see the last ran query on SQL Server 2005? This quick script uses sys.dm_exec_query_stats to list recent queries with the time each one ran.
Comparing Server Settings Across Instances
Comparing server settings across SQL Server instances shows configured and live values, so you can judge which differences affect a workload.