Tested on server version: SQL Server 2016 Developer Edition Reference info accurate on: January, 2018 was wanting to make THIS stored procedure:
Tag: DDL
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
Return DDL from METADATA
Tested on server version: SQL Server 2016 Developer Edition Reference info accurate on: January, 2018 The following script demonstrates how to generate each column's DDL from metadata in Transact-SQL. Example Output: T-SQL [code language="sql"] DECLARE @rundate datetime2 = getdate(); DECLARE @default_collation nvarchar(128); -- = 'SQL_Latin1_General_CP1_CI_AS'; SELECT @default_collation = convert(sysname, serverproperty('collation')) SELECT TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME … Continue reading Return DDL from METADATA
Return all System Object Definitions (DDL)
Tested on server version: SQL Server 2016 Developer Edition Reference info accurate on: January, 2018 The following view returns a comprehensive list of all System Objects on a server. It returns the object name, with and without concatinating the schema to the name. The view can be created and executed as-is.