Tested on server version: SQL Server 2016 Developer Edition Reference info accurate on: January, 2018 was wanting to make THIS stored procedure:
Category: Scripts
Welcome to my personal collection of Transact-SQL queries and scripts. This stuff was collected over the span of many years, but everything I’ve posted here has been tested on the stated version of SQL Server. Of course, your mileage may vary.
Formatting Dates in T-SQL Using CONVERT with Style Codes
CodeProject Assume you've been asked to return a date stored as a datetime2 in a specific format using Transact-SQL. For SQL Server 2012+, one option is to use T-SQL's FORMAT() function. FORMAT() is used with either a .NET format string or a custom formatting string to determine the formatting that should be applied. Starting with … Continue reading Formatting Dates in T-SQL Using CONVERT with Style Codes
SQL Server 2016: Look up the Current UTC Offset and Daylight Savings Time indicator for any Time Zone
Sql Server 2016, ships with a new system catalog view that returns the current UTC Offset for a given time zone and whether the time zone is currently observing Daylight Savings Time. Fun fact: Not all UTC offsets are in one hour increments. Some are only 30 minutes. The time zones listed are the ones … Continue reading SQL Server 2016: Look up the Current UTC Offset and Daylight Savings Time indicator for any Time Zone
Obtain an Exclusive Lock to Rename a SQL Server Database
Tested on: SQL Server 2016 Developer Edition Accurate as of: January, 2018Use this script to rename a database when the SQL Server database could not be exclusively locked to perform the operation. [code language="sql"] -- First, set the database to single user mode ALTER DATABASE CodeSnippets SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Now we will … Continue reading Obtain an Exclusive Lock to Rename a SQL Server Database
Querying Stored Procedure and System Function Parameter Information
Tested on: SQL Server 2016 Developer Edition Accurate as of: January, 2018The following snippet generates a SQL Server View that displays information about the parameters that need to be passed in to System Objects. The data type information that is returned in this view is human readable (i.e. `nvarchar(200) NULL`), rather than the numeric type … Continue reading Querying Stored Procedure and System Function Parameter Information
Show ROW NUMBERS in T-SQL RESULT set
Tested on: SQL Server 2016 Developer Edition Accurate as of: January, 2018 In this query, I've added a record number column to the result set using OVER and ORDER BY clauses with the ROW_NUMBER function in T-SQL. Note that the ORDER BY clause is required. The OVER() clause tells the SQL Engine to sort data … Continue reading Show ROW NUMBERS in T-SQL RESULT set
SQL Server Admin: Identifying Active Transactions
Tested on: SQL Server 2016 Developer Edition Accurate as of: January, 2018One way to identify active tractions, is using DBCC OPENTRAN. DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the … Continue reading SQL Server Admin: Identifying Active Transactions