RSS Feed!

About me

I am a business intelligence developer working at Bizware since August 2015. In this blog I will present code samples, primarilly in tsql but also findings in all other parts of the Microsoft BI-stack.


Disclaimer: All code should be considered as "as-is", i.e. badly tested and not working until extensive testing have been made by anyone reading the code. :)


Happy coding!

Documentabler

Version 1.1

USE [master]
GO
CREATE DATABASE [documentabler] ON PRIMARY
( NAME = N'documentabler', FILENAME = N'C:\documentabler\documentabler.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'documentabler_log', FILENAME = N'C:\documentabler\documentabler_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [documentabler] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [documentabler].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [documentabler] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [documentabler] SET ANSI_NULLS OFF
GO
ALTER DATABASE [documentabler] SET ANSI_PADDING OFF
GO
ALTER DATABASE [documentabler] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [documentabler] SET ARITHABORT OFF
GO
ALTER DATABASE [documentabler] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [documentabler] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [documentabler] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [documentabler] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [documentabler] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [documentabler] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [documentabler] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [documentabler] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [documentabler] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [documentabler] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [documentabler] SET DISABLE_BROKER
GO
ALTER DATABASE [documentabler] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [documentabler] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [documentabler] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [documentabler] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [documentabler] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [documentabler] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [documentabler] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [documentabler] SET READ_WRITE
GO
ALTER DATABASE [documentabler] SET RECOVERY FULL
GO
ALTER DATABASE [documentabler] SET MULTI_USER
GO
ALTER DATABASE [documentabler] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [documentabler] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'documentabler', N'ON'
GO
USE [documentabler]
GO
/****** Object: Table [dbo].[dt_view] Script Date: 04/01/2012 19:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dt_view](
[viewKey] [int] IDENTITY(1,1) NOT NULL,
[isLatest] [bit] NOT NULL,
[versionNr] [int] NOT NULL,
[latestVersionNr] [int] NOT NULL,
[isViewActive] [bit] NOT NULL,
[viewID] [bigint] NOT NULL,
[dbName] [nvarchar](150) NOT NULL,
[schemaName] [nvarchar](150) NOT NULL,
[viewName] [nvarchar](150) NOT NULL,
[viewCreateDate] [datetime2](7) NULL,
[viewModifyDate] [datetime2](7) NULL,
[viewDefinition] [nvarchar](max) NULL,
[rowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[viewKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[dt_table] Script Date: 04/01/2012 19:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dt_table](
[columnKey] [int] IDENTITY(1,1) NOT NULL,
[isLatest] [bit] NOT NULL,
[versionNr] [int] NOT NULL,
[latestVersionNr] [int] NOT NULL,
[isColumnActive] [bit] NOT NULL,
[columnID] [bigint] NULL,
[dbName] [nvarchar](128) NULL,
[schemaName] [nvarchar](128) NULL,
[tableName] [sysname] NOT NULL,
[columnName] [sysname] NULL,
[columnPosition] [int] NULL,
[columnDefault] [nvarchar](4000) NULL,
[isColumnNull] [int] NULL,
[columnDataType] [nvarchar](128) NULL,
[columnCharLength] [int] NULL,
[columnNumericPrecision] [tinyint] NULL,
[columnNumericScale] [int] NULL,
[columnDateTimePrecision] [smallint] NULL,
[columnCharacterSet] [sysname] NULL,
[columnCollationName] [sysname] NULL,
[isColumnConstraint] [int] NOT NULL,
[columnConstraint] [sysname] NULL,
[rowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL,
[columnExtendedProperty] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED
(
[columnKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[dt_object] Script Date: 04/01/2012 19:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dt_object](
[objectKey] [int] IDENTITY(1,1) NOT NULL,
[isLatest] [bit] NOT NULL,
[versionNr] [int] NOT NULL,
[latestVersionNr] [int] NOT NULL,
[isObjectActive] [bit] NOT NULL,
[objectId] [bigint] NULL,
[objectOldId] [bigint] NOT NULL,
[objectName] [nvarchar](150) NOT NULL,
[objectType] [nvarchar](5) NOT NULL,
[objectTypeName] [nvarchar](100) NULL,
[objectDefinition] [nvarchar](max) NULL,
[objectCreateDate] [datetime] NOT NULL,
[objectModifiedDate] [datetime] NOT NULL,
[objectSchemaName] [nvarchar](150) NOT NULL,
[objectDBName] [nvarchar](150) NOT NULL,
[RowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[objectKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[dt_index] Script Date: 04/01/2012 19:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dt_index](
[indexKey] [int] IDENTITY(1,1) NOT NULL,
[isLatest] [bit] NOT NULL,
[versionNr] [int] NOT NULL,
[latestVersionNr] [int] NOT NULL,
[isIndexActive] [bit] NOT NULL,
[indexId] [bigint] NULL,
[indexName] [nvarchar](150) NULL,
[indexDBName] [nvarchar](150) NULL,
[indexColumnNumber] [int] NOT NULL,
[indexcolumnName] [nvarchar](150) NULL,
[indexType] [tinyint] NOT NULL,
[indexTypeName] [nvarchar](60) NULL,
[indexIsUnique] [bit] NULL,
[indexIsPrimaryKey] [bit] NULL,
[indexKeyOrdinal] [tinyint] NOT NULL,
[indexIsDescendingKey] [bit] NULL,
[indexIsIncludedColumn] [bit] NULL,
[RowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[indexKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[v_exportExtendedPropertiesToExcel] Script Date: 04/01/2012 19:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS ******/
CREATE VIEW [dbo].[v_exportExtendedPropertiesToExcel]
as

SELECT TOP 1000000
[dbName]
,[schemaName]
,[tableName]
,[columnName]
,isnull(columnExtendedProperty, '') as OldColumnExtendedProperty
,'' as NewColumnExtendedProperty
,'=CONCATENATE("execute [documentabler].[dbo].[sp_updateExtendedProperty] ''' + [dbName] + ''', ''' + [schemaName] + ''', ''' + [tableName] + ''', ''' + [columnName] + ''',''" & F' +cast(1 + ROW_NUMBER() OVER (ORDER BY dbName, schemaName, tableName, columnName) as nvarchar(10))+ '&"''")' as SQLScript
FROM [documentabler].[dbo].[dt_table]
WHERE isLatest = 1
ORDER BY dbName, schemaName, tableName, columnName
GO
/****** Object: StoredProcedure [dbo].[spUpdate_dt_view] Script Date: 04/01/2012 19:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Langell (http://www.robinlangell.com)
-- Create date: 2012-04-01
-- Description: Inserts or updates view information on selected database
-- example:
-- exec dbo.spUpdate_dt_view 'documentabler'

-- =============================================
CREATE procedure [dbo].[spUpdate_dt_view]
(
@exportFromDBName nvarchar(100)
)
as
begin

create table #src
(
[viewID] bigint
,[dbName] nvarchar(150)
,[schemaName] nvarchar(150)
,[viewName] nvarchar(150)
,[viewCreateDate] datetime2
,[viewModifyDate] datetime2
,[viewDefinition] nvarchar(max)
,[rowContent] varbinary(8000)
,[validFrom] datetime2
,[validTo] datetime2
)

declare @sql nvarchar(max)

set @sql =
N'
INSERT INTO #src
select
binary_checksum(hashbytes(''SHA1'', isv.Table_catalog + isv.TABLE_SCHEMA + v.name)) as viewID,
cast(isv.TABLE_CATALOG as nvarchar(150)) as dbName,
cast(isv.TABLE_SCHEMA as nvarchar(150))as schemaName,
cast(v.name as nvarchar(150)) as viewName,
cast(v.create_date as datetime2) as viewCreateDate,
cast(v.modify_date as datetime2) as viewModifyDate,
cast(isv.VIEW_DEFINITION as nvarchar(max)) as viewDefinition,
hashbytes(''SHA1'',
isv.TABLE_CATALOG +
isv.TABLE_SCHEMA +
v.name +
isnull(cast(v.create_date as nvarchar(20)), '''') +
isnull(cast(v.modify_date as nvarchar(20)), '''') +
isnull(cast(isv.VIEW_DEFINITION as nvarchar(MAX)), '''')
) as RowContent,
cast(sysdatetime() as datetime2) as validFrom,
cast(''2099-12-31'' as datetime2) as validTo
from '+@exportFromDBName+'.sys.views v
JOIN '+@exportFromDBName+'.INFORMATION_SCHEMA.VIEWS isv ON v.name = isv.TABLE_NAME
'

execute sp_executesql @sql

PRINT 'Found ' + cast(@@ROWCOUNT as nvarchar(10)) + ' views in ' +@exportFromDBName

-- Insert new rows with no match in dt_view:
INSERT INTO documentabler..dt_view
(
[viewID]
,[dbName]
,[schemaName]
,[viewName]
,[viewCreateDate]
,[viewModifyDate]
,[viewDefinition]
,[rowContent]
,[validFrom]
,[validTo]
)
SELECT
s.[viewID]
,s.[dbName]
,s.[schemaName]
,s.[viewName]
,s.[viewCreateDate]
,s.[viewModifyDate]
,s.[viewDefinition]
,s.[rowContent]
,s.[validFrom]
,s.[validTo]
FROM #src s
LEFT JOIN documentabler.dbo.dt_view trg ON s.viewID = trg.viewID
WHERE
trg.viewID is null

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' new column rows in dt_view'

-- Save the rows to a table for later insert of SCD 2 New Rows usage
SELECT
trg.viewID,
trg.RowContent
INTO #oldColumnContent
FROM #src s
JOIN documentabler.dbo.dt_view trg ON s.viewID = trg.viewID
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

-- SCD 2 -- old rows, unset flags and limit time
UPDATE trg
set
trg.isLatest = 0,
trg.validTo = DATEADD(S, -1, s.[validFrom])
FROM documentabler.dbo.dt_view trg
JOIN #src s ON s.viewID = trg.viewID
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows in dt_view (curent SCD2 row)'

-- SCD2 insert new row
INSERT INTO documentabler..dt_view
(
[viewID]
,[dbName]
,[schemaName]
,[viewName]
,[viewCreateDate]
,[viewModifyDate]
,[viewDefinition]
,[rowContent]
,[validFrom]
,[validTo]
,isLatest
)
SELECT
s.[viewID]
,s.[dbName]
,s.[schemaName]
,s.[viewName]
,s.[viewCreateDate]
,s.[viewModifyDate]
,s.[viewDefinition]
,s.[rowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
FROM #src s
JOIN documentabler.dbo.dt_view trg ON trg.[viewID] = s.[viewID]
WHERE
trg.rowContent <> s.RowContent
-- to get just one row:
AND trg.rowContent IN (SELECT rowContent FROM #oldColumnContent)

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' SCD 2 rows in dt_view'

-- backwards update to get versioning right
UPDATE trg
SET trg.versionNr = src.NewVersionNr
FROM documentabler..dt_view trg
JOIN
(
SELECT
viewID,
dbName,
schemaName,
viewName,
versionNr,
latestVersionNr,
validFrom,
validTo,
DENSE_RANK() OVER (PARTITION BY viewID, dbName, schemaName, viewName ORDER BY validFrom) as NewVersionNr
FROM documentabler..dt_view
) src ON
trg.viewID = src.viewID
AND trg.dbName = src.dbName
AND trg.schemaName = src.schemaName
AND trg.viewName = src.viewName
AND trg.validFrom = src.validFrom
AND trg.validTo = src.validTo
AND trg.versionNr <> src.NewVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with versioning in dt_view'

-- update latest version.
UPDATE trg
SET trg.latestVersionNr = src.MaxVersionNr
FROM documentabler..dt_view trg
JOIN
(SELECT viewID, dbName, schemaName, viewName, MAX(versionNr) as MaxVersionNr
FROM documentabler..dt_view
GROUP BY viewID, dbName, schemaName, viewName) src
ON trg.viewID = src.viewID
AND trg.dbName = src.dbName
AND trg.schemaName = src.schemaName
AND trg.viewName = src.viewName
AND trg.latestVersionNr <> src.MaxVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct LatestVersioning in dt_view'

UPDATE trg
set trg.isViewActive = src.isViewActive
FROM
documentabler.dbo.dt_view trg
JOIN
(
SELECT trg.viewID, trg.dbName, 0 as isViewActive
FROM (SELECT * FROM documentabler..dt_view WHERE isLatest = 1 AND dbName = @exportFromDBName) trg
LEFT JOIN #src ON #src.viewID = trg.viewID AND #src.dbName = trg.dbName
WHERE #src.viewID is null
) src ON trg.viewID = src.viewID AND trg.dbName = src.dbName

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct isViewActive in dt_view'

-- (drop tables)
drop table #src
drop table #oldColumnContent
--
end
GO
/****** Object: StoredProcedure [dbo].[spUpdate_dt_table] Script Date: 04/01/2012 19:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Langell (http://www.robinlangell.com)
-- Create date: 2012-04-01
-- Description: Inserts or updates table information (including extended properties on columns) on selected database
-- example:
-- exec dbo.spUpdate_dt_table 'documentabler'

-- =============================================
CREATE procedure [dbo].[spUpdate_dt_table]
(
@exportFromDBName nvarchar(100)
)
as
begin

create table #src
(
columnID bigint,
dbName nvarchar(128),
schemaName nvarchar(128),
tableName nvarchar(128),
columnName nvarchar(128),
columnPosition int,
columnDefault nvarchar(4000),
isColumnNull int,
columnDataType nvarchar(128),
columnCharLength int,
columnNumericPrecision tinyint,
columnNumericScale int,
columnDateTimePrecision smallint,
columnCharacterSet nvarchar(128),
columnCollationName nvarchar(128),
isColumnConstraint nvarchar(128),
columnConstraint nvarchar(128),
RowContent varbinary(8000),
validfrom datetime2,
validTo datetime2,
columnExtendedProperty nvarchar(1000)
)

declare @sql nvarchar(max)

set @sql =
N'
INSERT INTO #src
select
binary_checksum(hashbytes(''SHA1'', c.TABLE_CATALOG + c.TABLE_SCHEMA + c.TABLE_NAME + c.COLUMN_NAME)) as ColumnID,
c.TABLE_CATALOG as dbName,
c.TABLE_SCHEMA as schemaName,
c.TABLE_NAME as tableName,
c.COLUMN_NAME as columnName,
c.ORDINAL_POSITION as columnPosition,
c.COLUMN_DEFAULT as columnDefault,
case when c.IS_NULLABLE = ''YES'' then 1 when c.IS_NULLABLE = ''NO'' then 0 else null end as isColumnNull,
c.DATA_TYPE as columnDataType,
c.CHARACTER_MAXIMUM_LENGTH as columnCharLength,
c.NUMERIC_PRECISION as columnNumericPrecision,
c.NUMERIC_SCALE as columnNumericScale,
c.DATETIME_PRECISION as columnDateTimePrecision,
c.CHARACTER_SET_NAME as columnCharacterSet,
c.COLLATION_NAME as columnCollationName,
case when kcu.constraint_name > '''' then 1 else 0 end as isColumnConstraint,
kcu.constraint_name as columConstraint,
hashbytes(''SHA1'',
c.TABLE_CATALOG +
c.TABLE_SCHEMA +
c.TABLE_NAME +
c.COLUMN_NAME +
cast(c.ORDINAL_POSITION as nvarchar(5)) +
isnull(cast(c.COLUMN_DEFAULT as nvarchar(10)), '''') +
c.IS_NULLABLE +
c.DATA_TYPE +
isnull(cast(c.CHARACTER_MAXIMUM_LENGTH as nvarchar(10)), '''') +
isnull(cast(c.CHARACTER_MAXIMUM_LENGTH as nvarchar(10)) ,'''') +
isnull(cast(c.NUMERIC_PRECISION as nvarchar(10)), '''') +
isnull(cast(c.NUMERIC_SCALE as nvarchar(5)), '''') +
isnull(cast(c.DATETIME_PRECISION as nvarchar(5)), '''') +
isnull(c.CHARACTER_SET_NAME, '''') +
isnull(c.COLLATION_NAME, '''') +
isnull(kcu.constraint_name, '''') +
isnull(cast(extendedProperty.ColumnExtendedProperty as nvarchar(1000)), '''')
) as RowContent,
cast(sysdatetime() as datetime2) as validFrom,
cast(''2099-12-31'' as datetime2) as validTo,
extendedProperty.ColumnExtendedProperty as columnExtendedProperty
--INTO #src
from ' + @exportFromDBName + '.INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN ' + @exportFromDBName + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON
c.TABLE_CATALOG = kcu.TABLE_CATALOG
AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND c.TABLE_NAME = kcu.TABLE_NAME
AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN
(
SELECT '''+ @exportFromDBName +''' AS TABLE_CATALOG,
''dbo'' AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
convert(nvarchar(1000), value) AS [ColumnExtendedProperty]
FROM ' + @exportFromDBName + '.sys.extended_properties AS ep
INNER JOIN ' + @exportFromDBName + '.sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN ' + @exportFromDBName + '.sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1
) extendedProperty ON
c.TABLE_CATALOG = extendedProperty.TABLE_CATALOG
AND c.TABLE_SCHEMA = extendedProperty.TABLE_SCHEMA
AND c.TABLE_NAME = extendedProperty.TABLE_NAME
AND c.COLUMN_NAME = extendedProperty.COLUMN_NAME
'

execute sp_executesql @sql

PRINT 'Found ' + cast(@@ROWCOUNT as nvarchar(10)) + ' column rows in ' +@exportFromDBName

-- Insert new rows with no match in dt_table:
INSERT INTO documentabler..dt_table
(
[columnID]
,[dbName]
,[schemaName]
,[tableName]
,[columnName]
,[columnPosition]
,[columnDefault]
,[isColumnNull]
,[columnDataType]
,[columnCharLength]
,[columnNumericPrecision]
,[columnNumericScale]
,[columnDateTimePrecision]
,[columnCharacterSet]
,[columnCollationName]
,[isColumnConstraint]
,[columnConstraint]
,[rowContent]
,[validFrom]
,[validTo]
,columnExtendedProperty
)
SELECT
s.[columnID]
,s.[dbName]
,s.[schemaName]
,s.[tableName]
,s.[columnName]
,s.[columnPosition]
,s.[columnDefault]
,s.[isColumnNull]
,s.[columnDataType]
,s.[columnCharLength]
,s.[columnNumericPrecision]
,s.[columnNumericScale]
,s.[columnDateTimePrecision]
,s.[columnCharacterSet]
,s.[columnCollationName]
,s.[isColumnConstraint]
,s.[columnConstraint]
,s.[rowContent]
,s.[validFrom]
,s.[validTo]
,s.columnExtendedProperty
FROM #src s
LEFT JOIN documentabler.dbo.dt_table trg ON s.[columnID] = trg.[columnID]
WHERE
trg.columnID is null

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' new column rows in dt_table'

-- Save the rows to a table for later insert of SCD 2 New Rows usage
SELECT
trg.ColumnID,
trg.RowContent
INTO #oldColumnContent
FROM #src s
JOIN documentabler.dbo.dt_table trg ON s.ColumnID = trg.columnID
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

-- SCD 2 -- old rows, unset flags and limit time
UPDATE trg
set
trg.isLatest = 0,
trg.validTo = DATEADD(S, -1, s.[validFrom])
FROM documentabler.dbo.dt_table trg
JOIN #src s ON s.ColumnID = trg.columnID
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows in dt_table'

-- SCD2 insert new row
INSERT INTO documentabler..dt_table
(
[columnID]
,[dbName]
,[schemaName]
,[tableName]
,[columnName]
,[columnPosition]
,[columnDefault]
,[isColumnNull]
,[columnDataType]
,[columnCharLength]
,[columnNumericPrecision]
,[columnNumericScale]
,[columnDateTimePrecision]
,[columnCharacterSet]
,[columnCollationName]
,[isColumnConstraint]
,[columnConstraint]
,[rowContent]
,[validFrom]
,[validTo]
,isLatest
,columnExtendedProperty
)
SELECT
s.[columnID]
,s.[dbName]
,s.[schemaName]
,s.[tableName]
,s.[columnName]
,s.[columnPosition]
,s.[columnDefault]
,s.[isColumnNull]
,s.[columnDataType]
,s.[columnCharLength]
,s.[columnNumericPrecision]
,s.[columnNumericScale]
,s.[columnDateTimePrecision]
,s.[columnCharacterSet]
,s.[columnCollationName]
,s.[isColumnConstraint]
,s.[columnConstraint]
,s.[rowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
,s.columnExtendedProperty
FROM #src s
JOIN documentabler.dbo.dt_table trg ON trg.[columnID] = s.[columnID]
WHERE
trg.rowContent <> s.RowContent
-- to get just one row:
AND trg.rowContent IN (SELECT rowContent FROM #oldColumnContent)

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' SCD 2 rows in dt_table'

-- backwards update to get versioning right
UPDATE trg
SET trg.versionNr = src.NewVersionNr
FROM documentabler..dt_table trg
JOIN
(
SELECT
columnID,
dbName,
schemaName,
tableName,
columnName,
versionNr,
latestVersionNr,
validFrom,
validTo,
DENSE_RANK() OVER (PARTITION BY columnID, dbName, schemaName, tableName, columnName ORDER BY validFrom) as NewVersionNr
FROM documentabler..dt_table
) src ON
trg.columnID = src.columnID
AND trg.dbName = src.dbName
AND trg.schemaName = src.schemaName
AND trg.tableName = src.tableName
AND trg.columnName = src.columnName
AND trg.validFrom = src.validFrom
AND trg.validTo = src.validTo
AND trg.versionNr <> src.NewVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with versioning in dt_table'

-- update latest version.
UPDATE trg
SET trg.latestVersionNr = src.MaxVersionNr
FROM documentabler..dt_table trg
JOIN
(SELECT columnID, dbName, schemaName, tableName, columnName, MAX(versionNr) as MaxVersionNr
FROM documentabler..dt_table
GROUP BY columnID, dbName, schemaName, tableName, columnName) src
ON trg.columnID = src.columnID
AND trg.dbName = src.dbName
AND trg.schemaName = src.schemaName
AND trg.tableName = src.tableName
AND trg.columnName = src.columnName
AND trg.latestVersionNr <> src.MaxVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with coorect LatestVersioning in dt_table'

UPDATE trg
set trg.isColumnActive = src.isColumnActive
FROM
documentabler.dbo.dt_table trg
JOIN
(
SELECT trg.columnID, trg.dbName, 0 as isColumnActive
FROM (SELECT * FROM documentabler..dt_table WHERE isLatest = 1 AND dbName = @exportFromDBName) trg
LEFT JOIN #src ON #src.columnID = trg.columnID AND #src.dbName = trg.dbName
WHERE #src.columnID is null
) src ON trg.columnID = src.columnID AND trg.dbName = src.dbName

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct isColumnActive in dt_table'

-- (drop tables)
drop table #src
drop table #oldColumnContent
--
end
GO
/****** Object: StoredProcedure [dbo].[spUpdate_dt_object] Script Date: 04/01/2012 19:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Langell (http://www.robinlangell.com)
-- Create date: 2012-04-01
-- Description: Inserts or updates object information (stored procedures, functions and views) on selected database
-- example:
-- exec dbo.spUpdate_dt_object 'documentabler'

-- =============================================
CREATE procedure [dbo].[spUpdate_dt_object]
(
@exportFromDBName nvarchar(100)
)
as
begin

create table #src
(
[objectId] [bigint] NULL,
[objectOldId] [bigint] NOT NULL,
[objectName] nvarchar(150) NOT NULL,
[objectType] [nvarchar](5) NOT NULL,
[objectTypeName] [nvarchar](100) NULL,
[objectDefinition] [nvarchar](max) NULL,
[objectCreateDate] [datetime] NOT NULL,
[objectModifiedDate] [datetime] NOT NULL,
[objectSchemaName] nvarchar(150) NOT NULL,
[objectDBName] [nvarchar](150) NOT NULL,
[RowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL
)

declare @sql nvarchar(max)

set @sql =
N'
INSERT INTO #src
SELECT
cast(binary_checksum(hashbytes(''SHA1'', cast(o.object_id as nvarchar(20)) + o.name + o.type_desc)) as bigint) as objectId,
cast(o.object_id as bigint) as objectOldId,
cast(o.name as nvarchar(150)) as objectName,
cast(o.type as nvarchar(5)) as objectType,
cast(o.type_desc as nvarchar(100)) as objectTypeName,
sm.definition as objectDefinition,
o.create_date as objectCreateDate,
o.modify_date as objectModifiedDate,
cast(s.name as nvarchar(150)) as objectSchemaName,
cast('''+ @exportFromDBName +''' as nvarchar(150)) as objectDBName,
hashbytes(''SHA1'',
o.name +
o.type +
-- isnull(cast(sm.definition as nvarchar(max)), '''') +
o.type_desc +
isnull(cast(o.create_date as nvarchar(20)), '''') +
isnull(cast(o.modify_date as nvarchar(20)), '''')
) as RowContent
,cast(sysdatetime() as datetime2) as validFrom
,cast(''2099-12-31'' as datetime2) as validTo
FROM ' +@exportFromDBName+ '.sys.sql_modules AS sm
JOIN ' +@exportFromDBName+ '.sys.objects AS o ON sm.object_id = o.object_id
JOIN ' +@exportFromDBName+ '.sys.schemas AS s ON o.schema_id = s.schema_id
'

execute sp_executesql @sql

PRINT 'Found ' + cast(@@ROWCOUNT as nvarchar(10)) + ' objects in ' +@exportFromDBName

-- Insert new rows with no match in dt_view:
INSERT INTO documentabler..dt_object
(
[objectId]
,[objectOldId]
,[objectName]
,[objectType]
,[objectTypeName]
,[objectDefinition]
,[objectCreateDate]
,[objectModifiedDate]
,[objectSchemaName]
,[objectDBName]
,[RowContent]
,[validFrom]
,[validTo]
,isLatest
)
SELECT
s.[objectId]
,s.[objectOldId]
,s.[objectName]
,s.[objectType]
,s.[objectTypeName]
,s.[objectDefinition]
,s.[objectCreateDate]
,s.[objectModifiedDate]
,s.[objectSchemaName]
,s.[objectDBName]
,s.[RowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
FROM #src s
LEFT JOIN documentabler.dbo.dt_object trg ON s.[objectId] = trg.[objectId]
WHERE
trg.[objectId] is null

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' new object rows in dt_object'

-- Save the rows to a table for later insert of SCD 2 New Rows usage
SELECT
trg.[objectId],
trg.RowContent
INTO #oldColumnContent
FROM #src s
JOIN documentabler.dbo.dt_object trg ON s.[objectId] = trg.[objectId]
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

-- SCD 2 -- old rows, unset flags and limit time
UPDATE trg
set
trg.isLatest = 0,
trg.validTo = DATEADD(S, -1, s.[validFrom])
FROM documentabler.dbo.dt_object trg
JOIN #src s ON s.[objectId] = trg.[objectId]
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows in dt_object (current SCD2 row)'

-- SCD2 insert new row
INSERT INTO documentabler..dt_object
(
[objectId]
,[objectOldId]
,[objectName]
,[objectType]
,[objectTypeName]
,[objectDefinition]
,[objectCreateDate]
,[objectModifiedDate]
,[objectSchemaName]
,[objectDBName]
,[RowContent]
,[validFrom]
,[validTo]
,isLatest
)
SELECT
s.[objectId]
,s.[objectOldId]
,s.[objectName]
,s.[objectType]
,s.[objectTypeName]
,s.[objectDefinition]
,s.[objectCreateDate]
,s.[objectModifiedDate]
,s.[objectSchemaName]
,s.[objectDBName]
,s.[RowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
FROM #src s
JOIN documentabler.dbo.dt_object trg ON trg.[objectId] = s.[objectId]
WHERE
trg.rowContent <> s.RowContent
-- to get just one row:
AND trg.rowContent IN (SELECT rowContent FROM #oldColumnContent)

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' SCD 2 rows in dt_object'

-- backwards update to get versioning right
UPDATE trg
SET trg.versionNr = src.NewVersionNr
FROM documentabler..dt_object trg
JOIN
(
SELECT
[objectId],
objectName,
objectDBName,
objectTypeName,
versionNr,
latestVersionNr,
validFrom,
validTo,
DENSE_RANK() OVER (PARTITION BY objectDBName, objectTypeName, [objectId] ORDER BY validFrom) as NewVersionNr
FROM documentabler..dt_object
) src ON
trg.objectId = src.objectId
AND trg.objectDBName = src.objectDBName
AND trg.objectTypeName = src.objectTypeName
AND trg.objectId = src.objectId
AND trg.validFrom = src.validFrom
AND trg.validTo = src.validTo
AND trg.versionNr <> src.NewVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with versioning in dt_object'

-- update latest version.
UPDATE trg
SET trg.latestVersionNr = src.MaxVersionNr
FROM documentabler..dt_object trg
JOIN
(SELECT objectDBName, objectTypeName, [objectId], MAX(versionNr) as MaxVersionNr
FROM documentabler..dt_object
GROUP BY objectDBName, objectTypeName, [objectId]) src
ON trg.objectDBName = src.objectDBName
AND trg.objectTypeName = src.objectTypeName
AND trg.[objectId] = src.[objectId]
AND trg.latestVersionNr <> src.MaxVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct LatestVersioning in dt_object'

UPDATE trg
set trg.isObjectActive = src.isObjectActive
FROM
documentabler.dbo.dt_object trg
JOIN
(
SELECT trg.objectId, trg.objectDBName, 0 as isObjectActive
FROM (SELECT * FROM documentabler..dt_object WHERE isLatest = 1 AND objectDBName = @exportFromDBName) trg
LEFT JOIN #src ON #src.objectId = trg.objectId AND trg.objectDBName = #src.objectDBName
WHERE #src.objectId is null
) src ON trg.objectId = src.objectId AND trg.objectDBName = src.objectDBName

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct isObjectActive in dt_object'

-- (drop tables)
drop table #src
drop table #oldColumnContent
--
end
GO
/****** Object: StoredProcedure [dbo].[spUpdate_dt_index] Script Date: 04/01/2012 19:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Langell (http://www.robinlangell.com)
-- Create date: 2012-04-01
-- Description: Inserts or updates index information on selected database
-- example:
-- exec dbo.spUpdate_dt_index 'documentabler'

-- =============================================
CREATE procedure [dbo].[spUpdate_dt_index]
(
@exportFromDBName nvarchar(100)
)
as
begin

create table #src
(
[indexId] [bigint] NULL,
[indexName] [nvarchar](150) NULL,
[indexColumnNumber] [int] NOT NULL,
[indexcolumnName] [nvarchar](150) NULL,
[indexType] [tinyint] NOT NULL,
[indexTypeName] [nvarchar](60) NULL,
[indexIsUnique] [bit] NULL,
[indexIsPrimaryKey] [bit] NULL,
[indexKeyOrdinal] [tinyint] NOT NULL,
[indexIsDescendingKey] [bit] NULL,
[indexIsIncludedColumn] [bit] NULL,
[RowContent] [varbinary](8000) NULL,
[validFrom] [datetime2](7) NULL,
[validTo] [datetime2](7) NULL,
indexDBName nvarchar(150) NULL
)

declare @sql nvarchar(max)

set @sql =
N'
INSERT INTO #src
SELECT
binary_checksum(hashbytes(''SHA1'', cast(ind.object_id as nvarchar(20)) + col.name + ind.type_desc)) as indexId
,ind.name as indexName
,ic.index_column_id as indexColumnNumber
,col.name as indexcolumnName
,ind.[type] as indexType
,ind.type_desc as indexTypeName
,ind.is_unique as indexIsUnique
,ind.is_primary_key as indexIsPrimaryKey
,ic.key_ordinal as indexKeyOrdinal
,ic.is_descending_key as indexIsDescendingKey
,ic.[is_included_column] as indexIsIncludedColumn
,hashbytes(''SHA1'',
ind.name +
isnull(cast(ic.index_column_id as nvarchar(5)), '''') +
col.name +
isnull(cast(ind.[type] as nvarchar(5)), '''') +
ind.type_desc +
isnull(cast(ind.is_unique as nvarchar(5)), '''')+
isnull(cast(ind.is_primary_key as nvarchar(5)), '''') +
isnull(cast(ic.key_ordinal as nvarchar(5)), '''') +
isnull(cast(ic.is_descending_key as nvarchar(5)), '''') +
isnull(cast(ic.[is_included_column] as nvarchar(5)), '''')
) as RowContent,
cast(sysdatetime() as datetime2) as validFrom,
cast(''2099-12-31'' as datetime2) as validTo,
'''+@exportFromDBName+''' as indexDBName
FROM '+@exportFromDBName+'.sys.indexes ind
INNER JOIN '+@exportFromDBName+'.sys.index_columns ic
ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN '+@exportFromDBName+'.sys.columns col
ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN '+@exportFromDBName+'.sys.tables t
ON ind.object_id = t.object_id
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
'

execute sp_executesql @sql

PRINT 'Found ' + cast(@@ROWCOUNT as nvarchar(10)) + ' indexcolumns in ' +@exportFromDBName

-- Insert new rows with no match in dt_view:
INSERT INTO documentabler..dt_index
(
[indexId]
,[indexName]
,[indexColumnNumber]
,[indexcolumnName]
,[indexType]
,[indexTypeName]
,[indexIsUnique]
,[indexIsPrimaryKey]
,[indexKeyOrdinal]
,[indexIsDescendingKey]
,[indexIsIncludedColumn]
,[RowContent]
,[validFrom]
,[validTo]
,isLatest
,indexDBName
)
SELECT
s.[indexId]
,s.[indexName]
,s.[indexColumnNumber]
,s.[indexcolumnName]
,s.[indexType]
,s.[indexTypeName]
,s.[indexIsUnique]
,s.[indexIsPrimaryKey]
,s.[indexKeyOrdinal]
,s.[indexIsDescendingKey]
,s.[indexIsIncludedColumn]
,s.[RowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
,s.indexDBName
FROM #src s
LEFT JOIN documentabler.dbo.dt_index trg ON s.indexId = trg.indexId
WHERE
trg.indexId is null

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' new column rows in dt_index'

-- Save the rows to a table for later insert of SCD 2 New Rows usage
SELECT
trg.indexId,
trg.RowContent
INTO #oldColumnContent
FROM #src s
JOIN documentabler.dbo.dt_index trg ON s.indexId = trg.indexId
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

-- SCD 2 -- old rows, unset flags and limit time
UPDATE trg
set
trg.isLatest = 0,
trg.validTo = DATEADD(S, -1, s.[validFrom])
FROM documentabler.dbo.dt_index trg
JOIN #src s ON s.indexId = trg.indexId
WHERE trg.rowContent <> s.RowContent
AND trg.isLatest = 1

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows in dt_index (current SCD2 row)'

-- SCD2 insert new row
INSERT INTO documentabler..dt_index
(
[indexId]
,[indexName]
,[indexColumnNumber]
,[indexcolumnName]
,[indexType]
,[indexTypeName]
,[indexIsUnique]
,[indexIsPrimaryKey]
,[indexKeyOrdinal]
,[indexIsDescendingKey]
,[indexIsIncludedColumn]
,[RowContent]
,[validFrom]
,[validTo]
,isLatest
,indexDBNAme
)
SELECT
s.[indexId]
,s.[indexName]
,s.[indexColumnNumber]
,s.[indexcolumnName]
,s.[indexType]
,s.[indexTypeName]
,s.[indexIsUnique]
,s.[indexIsPrimaryKey]
,s.[indexKeyOrdinal]
,s.[indexIsDescendingKey]
,s.[indexIsIncludedColumn]
,s.[RowContent]
,s.[validFrom]
,s.[validTo]
,1 as isLatest
,s.indexDBName
FROM #src s
JOIN documentabler.dbo.dt_index trg ON trg.indexid = s.indexId
WHERE
trg.rowContent <> s.RowContent
-- to get just one row:
AND trg.rowContent IN (SELECT rowContent FROM #oldColumnContent)

PRINT 'Inserted ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' SCD 2 rows in dt_index'

-- backwards update to get versioning right
UPDATE trg
SET trg.versionNr = src.NewVersionNr
FROM documentabler..dt_index trg
JOIN
(
SELECT
indexID,
indexName,
indexColumnNumber,
indexcolumnName,
versionNr,
latestVersionNr,
validFrom,
validTo,
DENSE_RANK() OVER (PARTITION BY indexID, indexName, indexColumnNumber, indexColumnName ORDER BY validFrom) as NewVersionNr
FROM documentabler..dt_index
) src ON
trg.indexId = src.indexId
AND trg.indexName = src.indexName
AND trg.indexColumnNumber = src.indexColumnNumber
AND trg.indexColumnName = src.indexColumnName
AND trg.validFrom = src.validFrom
AND trg.validTo = src.validTo
AND trg.versionNr <> src.NewVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with versioning in dt_index'

-- update latest version.
UPDATE trg
SET trg.latestVersionNr = src.MaxVersionNr
FROM documentabler..dt_index trg
JOIN
(SELECT indexid, indexName, indexColumnNumber, indexcolumnName, MAX(versionNr) as MaxVersionNr
FROM documentabler..dt_index
GROUP BY indexid, indexName, indexColumnNumber, indexcolumnName) src
ON trg.indexid = src.indexid
AND trg.indexName = src.indexName
AND trg.indexColumnNumber = src.indexColumnNumber
AND trg.indexcolumnName = src.indexcolumnName
AND trg.latestVersionNr <> src.MaxVersionNr

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct LatestVersioning in dt_index'

UPDATE trg
set trg.isIndexActive = src.isIndexActive
FROM
documentabler.dbo.dt_index trg
JOIN
(
SELECT trg.indexId, trg.indexDBName, 0 as isIndexActive
FROM (SELECT * FROM documentabler..dt_index WHERE isLatest = 1 AND indexDBName = @exportFromDBName) trg
LEFT JOIN #src ON #src.indexId = trg.indexId AND #src.indexDBName = trg.indexDBName
WHERE #src.indexId is null
) src ON trg.indexId = src.indexId AND trg.indexDBName = src.indexDBName

PRINT 'Updated ' + cast(isnull(@@ROWCOUNT, 0) as nvarchar(10)) + ' rows with correct isIndexActive in dt_index'

-- (drop tables)
drop table #src
drop table #oldColumnContent
--
end
GO
/****** Object: StoredProcedure [dbo].[sp_ExtendedPropertyUpdateInsert] Script Date: 04/01/2012 19:10:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Langell (http://www.robinlangell.com)
-- Create date: 2012-04-01
-- Description: Inserts or updates extended properties
-- example:
-- execute [documentabler].[dbo].[sp_ExtendedPropertyUpdateInsert] 'DBSample', 'dbo', 'Article', 'ArticleGroup','ArticleGroup of Articles aalmasda'

-- =============================================
CREATE PROCEDURE [dbo].[sp_ExtendedPropertyUpdateInsert]
-- Add the parameters for the stored procedure here
@dbName nvarchar(150),
@schemaName nvarchar(150),
@tableName nvarchar(150),
@columnName nvarchar(150),
@extendedProperty nvarchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF (@extendedProperty = '')
BEGIN
RETURN
END
ELSE IF(EXISTS (
SELECT * FROM [documentabler].[dbo].[dt_table]
WHERE @dbName = dbName
AND @schemaName = schemaName
AND @tableName = tableName
AND @columnName = columnName
AND (columnExtendedProperty is not null)
AND columnExtendedProperty <> @extendedProperty
AND isLatest = 1
)
)
BEGIN

declare @updateSql nvarchar(max) =

'EXEC '+@dbName+'.sys.sp_updateextendedproperty @name = N''Caption''
,@value = '''+ @extendedProperty + '''
,@level0type = N''Schema'', @level0name = '''+ @schemaName+ '''
,@level1type = N''Table'', @level1name = '''+ @tableName+ '''
,@level2type = N''Column'', @level2name = '''+ @columnName+ ''''

execute sp_executesql @updateSql

PRINT 'Updated extended property. ' + @dbName + '.' + @schemaName + '.' + '.' + @tableName + ' (' + @columnName + ', ' + @extendedProperty + ')'

PRINT 'Reloading documentabler.dbo.dt_table'
PRINT '------'
execute documentabler.dbo.spUpdate_dt_table @dbName
PRINT 'Reload done!'

END
ELSE
BEGIN

IF(EXISTS (
SELECT * FROM [documentabler].[dbo].[dt_table]
WHERE @dbName = dbName
AND @schemaName = schemaName
AND @tableName = tableName
AND @columnName = columnName
AND (columnExtendedProperty is not null)
AND columnExtendedProperty <> @extendedProperty
AND isLatest = 1
)
)
BEGIN
declare @addSql nvarchar(max) =

'EXEC '+@dbName+'.sys.sp_addextendedproperty @name = N''Caption''
,@value = '''+ @extendedProperty + '''
,@level0type = N''Schema'', @level0name = '''+ @schemaName+ '''
,@level1type = N''Table'', @level1name = '''+ @tableName+ '''
,@level2type = N''Column'', @level2name = '''+ @columnName+ ''''

execute sp_executesql @addSql

PRINT 'Added extended property. ' + @dbName + '.' + @schemaName + '.' + '.' + @tableName + ' (' + @columnName + ', ' + @extendedProperty + ')'

PRINT 'Reloading documentabler.dbo.dt_table'
PRINT '------'
execute documentabler.dbo.spUpdate_dt_table @dbName
PRINT 'Reload done!'

END
ELSE PRINT 'No change for extended property. ' + @dbName + '.' + @schemaName + '.' + '.' + @tableName + ' (' + @columnName + ', ' + @extendedProperty + ')'

END

END
GO
/****** Object: Default [DF__dt_view__isLates__46E78A0C] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_view] ADD DEFAULT ((1)) FOR [isLatest]
GO
/****** Object: Default [DF__dt_view__version__47DBAE45] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_view] ADD DEFAULT ((1)) FOR [versionNr]
GO
/****** Object: Default [DF__dt_view__latestV__48CFD27E] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_view] ADD DEFAULT ((1)) FOR [latestVersionNr]
GO
/****** Object: Default [DF__dt_view__isViewA__49C3F6B7] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_view] ADD DEFAULT ((1)) FOR [isViewActive]
GO
/****** Object: Default [DF__dt_table__isLate__2F10007B] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_table] ADD DEFAULT ((1)) FOR [isLatest]
GO
/****** Object: Default [DF__dt_table__versio__300424B4] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_table] ADD DEFAULT ((1)) FOR [versionNr]
GO
/****** Object: Default [DF__dt_table__latest__30F848ED] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_table] ADD DEFAULT ((1)) FOR [latestVersionNr]
GO
/****** Object: Default [DF__dt_table__isColu__31EC6D26] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_table] ADD DEFAULT ((1)) FOR [isColumnActive]
GO
/****** Object: Default [DF__dt_object__isLat__0E6E26BF] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_object] ADD DEFAULT ((1)) FOR [isLatest]
GO
/****** Object: Default [DF__dt_object__versi__0F624AF8] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_object] ADD DEFAULT ((1)) FOR [versionNr]
GO
/****** Object: Default [DF__dt_object__lates__10566F31] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_object] ADD DEFAULT ((1)) FOR [latestVersionNr]
GO
/****** Object: Default [DF__dt_object__isObj__114A936A] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_object] ADD DEFAULT ((1)) FOR [isObjectActive]
GO
/****** Object: Default [DF__dt_index__isLate__03F0984C] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_index] ADD DEFAULT ((1)) FOR [isLatest]
GO
/****** Object: Default [DF__dt_index__versio__04E4BC85] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_index] ADD DEFAULT ((1)) FOR [versionNr]
GO
/****** Object: Default [DF__dt_index__latest__05D8E0BE] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_index] ADD DEFAULT ((1)) FOR [latestVersionNr]
GO
/****** Object: Default [DF__dt_index__isInde__06CD04F7] Script Date: 04/01/2012 19:10:08 ******/
ALTER TABLE [dbo].[dt_index] ADD DEFAULT ((1)) FOR [isIndexActive]
GO

 

 

Leave a Reply