To find a stored procedure execution count, query sys.dm_exec_procedure_stats and divide the total elapsed time by the count. This post builds a demo, shows the query, and explains why the numbers start again when a plan leaves the cache. It also fixes the object name lookup that returns NULL for other databases.
Run a Stored Procedure at Startup in SQL Server
To run a stored procedure at startup, create it in the master database and mark it with sp_procoption. This post builds a demo that writes one row every time the instance starts. It also covers the rules, how to check the setting, and how to turn it off again.
SQL SERVER – New DMV in SQL Server 2017 – sys.dm_os_enumerate_fixed_drives. A Replacement of xp_fixeddrives
SQL Server 2017 adds sys.dm_os_enumerate_fixed_drives, a new DMV that shows free space on each drive and replaces xp_fixeddrives. Here is the query I use.
How to Create Temp Table From Stored Procedure? – Interview Question of the Week #140
How do you create a Temp Table from a stored procedure when you do not know its result set? Here is the OPENROWSET approach I use, and its drawbacks.
SQL SERVER – How to Find UNIQUE Key Columns? – sp_special_columns
Want to know which columns uniquely identify a row in a table? The system stored procedure sp_special_columns tells you. Here is a quick demo.
SQL SERVER – System Procedure to List Out Table From Linked Server
The System Procedure sp_tables_ex lists table metadata from an existing linked server. I check its provider and login mapping separately.
How to Show Results of sp_spaceused in a Single Result? – Interview Question of the Week #135
How do you show results of sp_spaceused in a single result? My co-interviewer asked this. I think it is a poor interview question, but here is the answer.