rvegajr

SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name,
ty.name as type_name, c.max_length  
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty
    ON ty.system_type_id = c.system_type_id
WHERE c.name LIKE '%COLNAME%'
ORDER BY schema_name, table_name; 

rvegajr

# This script creates a copy of a database (minus data) on the same server
# set "Option Explicit" to catch subtle errors 
set-psdebug -strict
$servername='.' # server name and instance
$servernameLabel=$servername;
if ($servername -eq '.') {
	$servernameLabel = 'localhost';
}
$Database='CppeDB' # the database to copy from
$ServerDirectory =$null # we let the script find a suitable place for data etc.

rvegajr

IF OBJECT_ID (N'dbo.udfAttributesToJson', N'FN') IS NOT NULL  
    DROP FUNCTION udfAttributesToJson;  
GO  
CREATE FUNCTION dbo.udfAttributesToJson(@str VARCHAR(8000))  
RETURNS NVARCHAR(MAX)   
AS   
BEGIN  

	DECLARE @ret NVARCHAR(MAX);
	IF (LEN(@str)=0) RETURN '';

rvegajr

--http://vyaskn.tripod.com/sql_server_search_and_replace.htm
-- EXAMPLE

--To replace all occurences of 'America' with 'USA': 
--EXEC SearchAndReplace 'America', 'USA'
--GO 
--

CREATE PROC SearchAndReplace
(

rvegajr

EXEC sp_MSForEachTable '
	PRINT ''Setting temporal table system Versioning to on for ?'';
	DECLARE @sql VARCHAR(1024);
	SET @sql = ''ALTER TABLE ? SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = '' + REPLACE(REPLACE(''?'', '']'', ''''), ''['', '''') +''History))''
	PRINT ''  Executing @sql='' + @sql;
	EXEC(@sql);
'
GO

rvegajr

SELECT 
  name
  , delete_referential_action_desc
  , update_referential_action_desc
FROM 
  sys.foreign_keys

rvegajr

EXEC sp_MSForEachTable 'IF COL_LENGTH(''?'', ''SysStartTime'') IS NULL
BEGIN
	PRINT ''Creating temporal table for ?'';
	ALTER TABLE ?
		ADD 
		  SysActionUID VARCHAR(50) DEFAULT substring(SYSTEM_USER,charindex(''\'',SYSTEM_USER)+1,len(SYSTEM_USER)) WITH VALUES 
		, SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
			DEFAULT SYSUTCDATETIME()
		,

rvegajr

CREATE TRIGGER tgui_<tablename>_ActionStamp ON dbo.<tablename> FOR INSERT, DELETE
AS BEGIN
	SET NOCOUNT ON;
    IF UPDATE (SysActionUID) BEGIN
        UPDATE <tablename> 
        SET SysActionUID = (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
        FROM <tablename> a INNER JOIN Inserted I  ON a.SysActionUID = I.SysActionUID WHERE I.SysActionUID IS NULL
    END 
END

rvegajr

SET NOCOUNT ON
GO

PRINT 'Using Master database'
USE master
GO

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_merge','P')) IS NOT NULL --means, the procedure already exists
 BEGIN

rvegajr

-- For some god awful reason, this query takes 1 minute to run with COMPATLVL 13, this will change it to 11 fo this script only
DECLARE @sql NVARCHAR(1000) = 'ALTER DATABASE ' + DB_NAME() + ' SET COMPATIBILITY_LEVEL = 110';
EXECUTE sp_executesql @sql
GO
-- DO NOT INCLUDE THE ABOVE ON THE QUERY THAT WILL CALCUALTE MULTIPLICTY
SET NOCOUNT ON
/*
DROP TABLE #IDX;
DROP TABLE #COL;
DROP TABLE #FK;