Most people run SELECT * FROM sys.tables to list tables. If you do not need all those details, sys.sp_tables returns tables and views with schema names.
SQL SERVER – Encrypted Stored Procedure and Activity Monitor
Can you see the definition of an encrypted stored procedure in Activity Monitor? The answer is No. Here is a quick test that proves it.
SQL SERVER – Get Numeric Value From Alpha Numeric String – Get Numbers Only
Reader Christofer improved my logic to get a numeric value from alpha numeric string data. His stored procedure uses REVERSE and PATINDEX.
Return Codes: Keep Procedure Status Separate From Data
Return codes carry procedure status separately from application values. This private demonstration compares result sets, captured output and omitted OUTPUT. It also separates an optional no-work request from a genuine raised error.
SQL SERVER – Check Advanced Server Configuration
How do you Check Advanced Server Configuration values? Turn on show advanced options with sp_configure, run RECONFIGURE, then run sp_configure to list them.
SQL SERVER – TRANSACTION, DML and Schema Locks
Most candidates in a senior developer interview could not create a scenario that shows schema locks. Here is a simple demo with a TRANSACTION and DML.
SQL SERVER – Stored Procedure and Transactions
Is a stored procedure transactional by itself? No. A simple example with three tables shows why stored procedure and transactions are separate things.