rvegajr

DECLARE  @SchemaName varchar(100) = 'stg'
 ,@WorkTest char(1) = 'w'  -- use 'w' to work and 't' to print
 ,@dropSchema BIT = '1'

/*-----------------------------------------------------------------------------------------
 
  Author : Ranjith Kumar S
  Date:    31/01/10
 
  Description: It drop all the objects in a schema and then the schema itself

rvegajr

declare @tableName varchar(200)
declare @schemaName varchar(200)
declare @columnName varchar(200)
declare @nullable varchar(50)
declare @datatype varchar(50)
declare @maxlen int

declare @sType varchar(50)
declare @sProperty varchar(200)

rvegajr

{"lastUpload":"2021-03-17T17:19:05.098Z","extensionVersion":"v3.4.3"}

rvegajr

Contributing

When contributing to this repository, please first discuss the change you wish to make via issue, email, or any other method with the owners of this repository before making a change.

Please note we have a code of conduct, please follow it in all your interactions with the project.

Pull Request Process

1. Ensure any install or build dependencies are removed before the end of the layer when doing a

rvegajr

 
IF OBJECT_ID (N'udfExtractCDataAsXML', N'FN') IS NOT NULL  
    DROP FUNCTION udfExtractCDataAsXML;  
GO  
CREATE FUNCTION dbo.udfExtractCDataAsXML(@xmlAsText VARCHAR(max))  
RETURNS XML
AS   
BEGIN  
	--DECLARE @xmlAsText AS VARCHAR(max); SET @xmlAsText = '--MIMEBoundary_f7907934d12e085f0fea76f3b32e3f36b19cd0be1a9610cc\r\nContent-Type: application/xop+xml; charset=utf-8; type=\"text/xml\"\r\nContent-Transfer-Encoding: binary\r\nContent-ID: <0.04907934d12e085f0fea76f3b32e3f36b19cd0be1a9610cc@apache.org>\r\n\r\n<?xml version=''1.0'' encoding=''utf-8''?><![CDATA[ xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\"><soapenv:Header/><soapenv:Body><ns1:createBPRecordResponse xmlns:ns1=\"http://general.service.webservices.skire.com\"><ns1:return><ax21:statusCode xmlns:ax21=\"http://xml.util.webservices.skire.com/xsd\">200</ax21:statusCode><ax21:xmlcontents xmlns:ax21=\"http://xml.util.webservices.skire.com/xsd\"/><ax21:errorStatus xmlns:ax21=\"http://xml.util.webservices.skire.com/xsd\"/></ns1:return></n

rvegajr

IF OBJECT_ID (N'udfExtractSoapEnvelopeAsXML', N'FN') IS NOT NULL  
    DROP FUNCTION udfExtractSoapEnvelopeAsXML;  
GO  
CREATE FUNCTION dbo.udfExtractSoapEnvelopeAsXML(@xmlAsText VARCHAR(max))  
RETURNS XML
AS   
BEGIN  
	--DECLARE @xmlAsText AS VARCHAR(max); SET @xmlAsText = '--MIMEBoundary_f7907934d12e085f0fea76f3b32e3f36b19cd0be1a9610cc\r\nContent-Type: application/xop+xml; charset=utf-8; type=\"text/xml\"\r\nContent-Transfer-Encoding: binary\r\nContent-ID: <0.04907934d12e085f0fea76f3b32e3f36b19cd0be1a9610cc@apache.org>\r\n\r\n<?xml version=''1.0'' encoding=''utf-8''?><soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\"><soapenv:Header/><soapenv:Body><ns1:createBPRecordResponse xmlns:ns1=\"http://general.service.webservices.skire.com\"><ns1:return><ax21:statusCode xmlns:ax21=\"http://xml.util.webservices.skire.com/xsd\">200</ax21:statusCode><ax21:xmlcontents xmlns:ax21=\"http://xml.util.webservices.skire.com/xsd\"/><ax21:errorStatus xmlns:ax21=\"http://xml.util.webservices.skire.c

rvegajr

/*

EZSchemaWatcher - The goal of this script is to provide a simple cheap way to watch for schema changes on a single database.  This can be accomplished using this code in 2 steps.  
First, legalities: Naturally I am not liable for any damage this code may cause. :)
Second,  I don't care if you give me credit for this or not,  just share it with people who you think would find it useful,
and remember that we all in this together, so share your knowledge through open source.  
Any questions?  email me at r@noctusoft.com
 
STEP 1 - Change the script to match your environment.  Definitely rename the first 2 %%, the last 2 are optional
	

rvegajr

CREATE PROCEDURE [dbo].[uspMissingForeignKeyValues]
	@FKTableNameOrConstraintName VARCHAR(255),
	@FKTableKey VARCHAR(255)='',
	@TargetTableName VARCHAR(255)='',
	@TargetTableKey VARCHAR(255)=''
AS
SET NOCOUNT ON;
	DECLARE @sql NVARCHAR(4000)='';
	DECLARE @FKTableName VARCHAR(255) = '';

rvegajr

--Disable all check constraints on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
 
--Disable all triggers on all tables
EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
 
--Delete all data from all tables
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'

rvegajr

DECLARE @sqlBase VARCHAR(MAX)
SET @sqlbase = 'ALTER TABLE [dbo].XXX SET (SYSTEM_VERSIONING = ON  (HISTORY_TABLE = dbo.XXXTemporal));'

SELECT REPLACE(@sqlbase, 'XXX', LEFT(Table_Name, LEN(Table_Name)-8)), * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%temporal'