On-site performance tuning analysis can be as rewarding as it is frustrating. There are some aspects you just can’t control; Ranting managers or dealing with a passive DBA, which often stands behind you with popcorn and drinks. Fortunately, you can prepare your scripts in advance based as i did based on prior leanings.
During a recent session, I’ve discovered that I was missing a simple analysis script. A scrip which allows me to list the most important object properties within one overview. And so “sp_what” was born. The implementation was fairly simple — just combining some Catalog Views together with a search parameter.
|name||Name of the object|
|object_id||ID of the object, or the ID of the object to which this column belongs.|
|schema||Name of the schema and the ID|
|type_desc||Object type description|
|details||Prebuild Catalog Views query|
|details2||Additional details which differs based on the object type|
Note: If you are looking for sp_who, please try this link instead. https://msdn.microsoft.com/en-us/library/ms174313.aspx