/Main/DependencyAnalyzer2008/Repository.cs

Large files files are truncated, but you can click here to view the full file

///

/// Microsoft SQL Server 2005 Business Intelligence Metadata Reporting Samples

/// Dependency Analyzer Sample

/// 

/// Copyright (c) Microsoft Corporation.  All rights reserved.

///

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

using System.Data.Common;

using System.Data.OleDb;

using System.Diagnostics;



namespace Microsoft.Samples.DependencyAnalyzer

{

    class Repository : IDisposable

    {

#if SQL2005

        public const string OLEDBGuid = "{9B5D63AB-A629-4A56-9F3E-B1044134B649}";

#else

        public const string OLEDBGuid = "{3BA51769-6C3C-46B2-85A1-81E58DB7DAE1}";

#endif

        internal class Domains

        {

            internal const string SSIS = "SSIS";

            internal const string SSAS = "SSAS";

            internal const string SSRS = "SSRS";

            internal const string Relational = "RDBMS";

            internal const string File = "FILE";

            internal const string Other = "Other";

            internal const string Column = "COLUMN";

        }



        /// <summary>

        /// Stores the current Database Version

        /// </summary>

        const int _dbVersion = 6;



        /// <summary>

        ///  repository tables

        /// </summary>

        DataTable objectTable = new DataTable("Objects");

        DataTable objectDependenciesTable = new DataTable("ObjectDependencies");

        DataTable objectAttributesTable = new DataTable("ObjectAttributes");

        DataTable objectTypesTable = new DataTable("ObjectTypes");

        DataTable runScanTable = new DataTable("RunScan");



        internal class DependencyTypes

        {

            internal const string Containment = "Containment";

            internal const string Lineage = "Map";

            internal const string Use = "Use";

        }



        internal class ConnectionStringProperties

        {

            internal const string DataSource = "Data Source";

            internal const string Server = "Server";

            internal const string Location = "Location";

            internal const string Provider = "Provider";

            internal const string Database = "Database";

            internal const string InitialCatalog = "Initial Catalog";

        }



        internal class Attributes

        {

            internal const string ConnectionString = "ConnectionString";

            internal const string ConnectionServer = "Server";

            internal const string ConnectionDatabase = "Database";

            internal const string QueryDefinition = "QueryDefinition";

        }



        private SqlConnection repositoryConnection;



        /// <summary>

        ///  the root of the containments

        /// </summary>

        private int rootRepositoryObjectID = 0;



        public int RootRepositoryObjectID

        {

            get

            {

                return rootRepositoryObjectID;

            }

            set

            {

                rootRepositoryObjectID = value;

            }

        }





        public Repository(string connectionString)

        {

            repositoryConnection = new SqlConnection();

            repositoryConnection.ConnectionString = connectionString;

            databasePrefixExclusions = new List<string>();

        }



        private int runKeyValue = 0;

        public int RunKeyValue

        {

            get

            {

                return this.runKeyValue;

            }

            set

            {

                this.runKeyValue = value;

            }

        }



        private bool databaseNameOnlyCompare = false;

        public bool DatabaseNameOnlyCompare

        {

            get

            {

                return databaseNameOnlyCompare;

            }

            set

            {

                databaseNameOnlyCompare = value;

            }

        }



        private List<string> databasePrefixExclusions;

        public List<string> DatabasePrefixExclusions

        {

            get

            {

                return databasePrefixExclusions;

            }

        }



        public void Open()

        {

            repositoryConnection.Open();

            // TODO:

            // Reset the rootRepositoryObjectID to the maximum ObjectID + 1 (if > 0 records)...



            DataColumn column = objectTable.Columns.Add("RunKey");

            column.DataType = typeof(int);

            column = objectTable.Columns.Add("ObjectKey");

            column.AutoIncrement = true;

            column.AutoIncrementSeed = 0;  // ToDo: reset this to correct value...

            column.DataType = typeof(int);

            objectTable.Columns.Add("ObjectName");

            objectTable.Columns.Add("ObjectTypeString");

            objectTable.Columns.Add("ObjectDesc");



            column = objectDependenciesTable.Columns.Add("RunKey");

            column.DataType = typeof(int);

            column = objectDependenciesTable.Columns.Add("SrcObjectKey");

            column.DataType = typeof(int);

            column = objectDependenciesTable.Columns.Add("TgtObjectKey");

            column.DataType = typeof(int);

            column = objectDependenciesTable.Columns.Add("DependencyType");



            column = objectAttributesTable.Columns.Add("RunKey");

            column.DataType = typeof(int);

            column = objectAttributesTable.Columns.Add("ObjectKey");

            column.DataType = typeof(int);

            objectAttributesTable.Columns.Add("ObjectAttrName");

            objectAttributesTable.Columns.Add("ObjectAttrValue");



            objectTypesTable.Columns.Add("ObjectTypeID");

            objectTypesTable.Columns.Add("ObjectTypeName");

            objectTypesTable.Columns.Add("ObjectTypeDesc");

            objectTypesTable.Columns.Add("ObjectMetaType"); // todo: populate this column

            objectTypesTable.Columns.Add("Domain");



            column = runScanTable.Columns.Add("RunKey");

            column.AutoIncrement = true;

            column.AutoIncrementSeed = 0;  // ToDo: reset this to correct value...

            column.DataType = typeof(int);

            column = runScanTable.Columns.Add("RunDate");

            column.DataType = typeof(DateTime);

            column = runScanTable.Columns.Add("RunCommand");

        }



        public void Close()

        {

            repositoryConnection.Close();

        }



        public void Dispose()

        {

            repositoryConnection.Dispose();

        }



        public bool IsValidRepository()

        {

            DataTable schemaInfo = repositoryConnection.GetSchema("Tables");

            DataRow[] rows = schemaInfo.Select(string.Format("TABLE_NAME = 'RunScan'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'LookupConnectionID'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'Version'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'ObjectTypes'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'Objects'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'ObjectDependencies'"));

            if (rows.Length == 0)

            {

                return false;

            }

            rows = schemaInfo.Select(string.Format("TABLE_NAME = 'ObjectAttributes'"));

            if (rows.Length == 0)

            {

                return false;

            }

            using (SqlCommand sqlCommand = new SqlCommand("SELECT MAX(VersionID) FROM [dbo].[Version]", repositoryConnection))

            {

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())

                {

                    sqlReader.Read();

                    if (System.Convert.ToInt32(sqlReader[0]) != _dbVersion)

                    {

                        return false;

                    }

                }

            }

            return true;

        }



        public void Commit()

        {

            // write each data table to the database

            SqlBulkCopy bulkCopy = new SqlBulkCopy(repositoryConnection);



            bulkCopy.DestinationTableName = "RunScan";

            bulkCopy.WriteToServer(runScanTable);



            bulkCopy.DestinationTableName = "Objects";

            bulkCopy.WriteToServer(objectTable);



            bulkCopy.DestinationTableName = "ObjectDependencies";

            bulkCopy.WriteToServer(objectDependenciesTable);



            bulkCopy.DestinationTableName = "ObjectAttributes";

            bulkCopy.WriteToServer(objectAttributesTable);



            bulkCopy.DestinationTableName = "ObjectTypes";

            bulkCopy.WriteToServer(objectTypesTable);



            bulkCopy.Close();

        }



        public void CreateRepository()

        {

            int dbVersion = 0;

            int sqlVersion = 9;



            DataTable schemaInfo = repositoryConnection.GetSchema("Tables");

            DataRow[] rows = schemaInfo.Select(string.Format("TABLE_NAME = 'Version'"));

            if (rows.Length != 0)

            {

                using (SqlCommand sqlCommand = new SqlCommand("SELECT MAX(VersionID) FROM [dbo].[Version]", repositoryConnection))

                {

                    using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())

                    {

                        sqlReader.Read();

                        dbVersion = System.Convert.ToInt32(sqlReader[0]);

                    }

                }

            }

            using (SqlCommand sqlCommand = new SqlCommand("select left(cast(serverproperty('productversion') as nvarchar(128)), charindex(cast(serverproperty('productversion') as nvarchar(128)), '.') + 1)", repositoryConnection))

            {

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())

                {

                    sqlReader.Read();

                    sqlVersion = System.Convert.ToInt32(sqlReader[0]);

                }

            }



            if (dbVersion == 0)

            {

                #region dbVersion 0

                // The following database create will create a Version 4 database.

                // If the database is prior to Version 4 (No Version Table) then it will be dropped.

                // This is OK, as the previous version didn't support history!

                using (SqlCommand sqlCommand = repositoryConnection.CreateCommand())

                {

                    sqlCommand.CommandText = "SET ANSI_NULLS ON";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "SET QUOTED_IDENTIFIER ON";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectAttributes_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectAttributes]'))\r\n" +

                                            "	ALTER TABLE [dbo].[ObjectAttributes] DROP CONSTRAINT [FK_ObjectAttributes_Objects]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "	ALTER TABLE [dbo].[ObjectDependencies] DROP CONSTRAINT [FK_ObjectDependencies_Objects]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "	ALTER TABLE [dbo].[ObjectDependencies] DROP CONSTRAINT [FK_ObjectDependencies_Objects1]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_RunScan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objects]'))\r\n" +

                                            "	ALTER TABLE [dbo].[Objects] DROP CONSTRAINT [FK_Objects_RunScan]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectAttributes]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[ObjectAttributes]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[ObjectDependencies]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objects]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[Objects]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectTypes]') AND type in (N'U'))\r\n" +

                                            "\r\n" +

                                            "DROP TABLE [dbo].[ObjectTypes]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RunScan]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[RunScan]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Audit]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[Audit]\r\n" +

                                            "	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LookupConnectionID]') AND type in (N'U'))\r\n" +

                                            "	DROP TABLE [dbo].[LookupConnectionID]\r\n" +

                                            "END";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LookupConnectionID]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[LookupConnectionID](\r\n" +

                                            "	[ConnectionGUID] [nvarchar](1000) NOT NULL,\r\n" +

                                            "	[ConnectionDescription] [nvarchar](1000) NOT NULL\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{5F2826BC-648B-4f3e-B930-587F4EF331D4}', N'ODBC 2005')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{9B5D63AB-A629-4A56-9F3E-B1044134B649}', N'OLEDB 2005')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{72692A11-F5CC-42b8-869D-84E7C8E48B14}', N'ADO.NET 2005')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{4CF60474-BA87-4ac2-B9F3-B7B9179D4183}', N'ADO 2005')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'RelationalDataSource', N'olap relational data source')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{09AD884B-0248-42C1-90E6-897D1CD16D37}', N'ODBC 2008')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{3BA51769-6C3C-46B2-85A1-81E58DB7DAE1}', N'OLEDB 2008')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{A1100566-934E-470C-9ECE-0D5EB920947D}', N'ADO 2008')\r\n" +

                                            "INSERT [dbo].[LookupConnectionID] ([ConnectionGUID], [ConnectionDescription]) VALUES (N'{894CAE21-539F-46EB-B36D-9381163B6C4E}', N'ADO.Net 2008')\r\n" +

                                            "END\r\n";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Audit]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[Audit](\r\n" +

                                            "	[PackageGUID] [varchar](50) NOT NULL,\r\n" +

                                            "	[DataFlowTaskID] [int] NOT NULL,\r\n" +

                                            "	[SourceReadRows] [int] NULL,\r\n" +

                                            "	[SourceReadErrorRows] [int] NULL,\r\n" +

                                            "	[CleansedRows] [int] NULL,\r\n" +

                                            "	[TargetWriteRows] [int] NULL,\r\n" +

                                            "	[TargetWriteErrorRows] [int] NULL,\r\n" +

                                            "	[Comment] [nvarchar](255) NULL\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "END\r\n";

                    sqlCommand.ExecuteNonQuery();

                    if (sqlVersion > 9)

                    {

                        sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))\r\n" +

                                                "BEGIN\r\n" +

                                                "CREATE TABLE [dbo].[Version](\r\n" +

                                                "	[VersionID] [int] NOT NULL,\r\n" +

                                                "	[InstallDate] [date] NOT NULL,\r\n" +

                                                " CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED \r\n" +

                                                "(\r\n" +

                                                "	[VersionID] ASC\r\n" +

                                                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                                ") ON [PRIMARY]\r\n" +

                                                "END\r\n";

                        sqlCommand.ExecuteNonQuery();

                        sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RunScan]') AND type in (N'U'))\r\n" +

                                                "BEGIN\r\n" +

                                                "CREATE TABLE [dbo].[RunScan](\r\n" +

                                                "	[RunKey] [int] NOT NULL,\r\n" +

                                                "	[RunDate] [datetime2](7) NOT NULL,\r\n" +

                                                "	[RunCommand] [nvarchar](512) NOT NULL,\r\n" +

                                                " CONSTRAINT [PK_RunScan] PRIMARY KEY CLUSTERED \r\n" +

                                                "(\r\n" +

                                                "	[RunKey] ASC\r\n" +

                                                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                                ") ON [PRIMARY]\r\n" +

                                                "END\r\n" +

                                                "IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'RunScan', NULL,NULL))\r\n" +

                                                "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores a row for each execution of the DependancyAnalyzer program' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RunScan'\r\n" +

                                                "";

                    }

                    else

                    {

                        sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))\r\n" +

                                                "BEGIN\r\n" +

                                                "CREATE TABLE [dbo].[Version](\r\n" +

                                                "	[VersionID] [int] NOT NULL,\r\n" +

                                                "	[InstallDate] [datetime] NOT NULL,\r\n" +

                                                " CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED \r\n" +

                                                "(\r\n" +

                                                "	[VersionID] ASC\r\n" +

                                                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                                ") ON [PRIMARY]\r\n" +

                                                "END\r\n";

                        sqlCommand.ExecuteNonQuery();

                        sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RunScan]') AND type in (N'U'))\r\n" +

                                                "BEGIN\r\n" +

                                                "CREATE TABLE [dbo].[RunScan](\r\n" +

                                                "	[RunKey] [int] NOT NULL,\r\n" +

                                                "	[RunDate] [datetime] NOT NULL,\r\n" +

                                                "	[RunCommand] [nvarchar](512) NOT NULL,\r\n" +

                                                " CONSTRAINT [PK_RunScan] PRIMARY KEY CLUSTERED \r\n" +

                                                "(\r\n" +

                                                "	[RunKey] ASC\r\n" +

                                                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                                ") ON [PRIMARY]\r\n" +

                                                "END\r\n" +

                                                "IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'RunScan', NULL,NULL))\r\n" +

                                                "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores a row for each execution of the DependancyAnalyzer program' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'RunScan'\r\n" +

                                                "";

                    }

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectTypes]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[ObjectTypes](\r\n" +

                                            "	[ObjectTypeKey] [nvarchar](255) NOT NULL,\r\n" +

                                            "	[ObjectTypeName] [nvarchar](255) NULL,\r\n" +

                                            "	[ObjectTypeDesc] [nvarchar](2000) NULL,\r\n" +

                                            "	[ObjectMetaType] [nvarchar](255) NULL,\r\n" +

                                            "	[Domain] [nvarchar](50) NULL\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "END\r\n" +

                                            "";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objects]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[Objects](\r\n" +

                                            "	[RunKey] [int] NOT NULL,\r\n" +

                                            "	[ObjectKey] [int] NOT NULL,\r\n" +

                                            "	[ObjectName] [nvarchar](1000) NULL,\r\n" +

                                            "	[ObjectTypeString] [nvarchar](1000) NOT NULL,\r\n" +

                                            "	[ObjectDesc] [nvarchar](1000) NULL,\r\n" +

                                            " CONSTRAINT [PK_Objects] PRIMARY KEY CLUSTERED \r\n" +

                                            "(\r\n" +

                                            "	[RunKey] ASC,\r\n" +

                                            "	[ObjectKey] ASC\r\n" +

                                            ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "END\r\n" +

                                            "";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[ObjectDependencies](\r\n" +

                                            "	[RunKey] [int] NOT NULL,\r\n" +

                                            "	[SrcObjectKey] [int] NOT NULL,\r\n" +

                                            "	[TgtObjectKey] [int] NOT NULL,\r\n" +

                                            "	[DependencyType] [nvarchar](50) NOT NULL,\r\n" +

                                            " CONSTRAINT [PK_ObjectDependencies] PRIMARY KEY CLUSTERED \r\n" +

                                            "(\r\n" +

                                            "	[RunKey] ASC,\r\n" +

                                            "	[SrcObjectKey] ASC,\r\n" +

                                            "	[TgtObjectKey] ASC,\r\n" +

                                            "	[DependencyType] ASC\r\n" +

                                            ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "END\r\n" +

                                            "";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectAttributes]') AND type in (N'U'))\r\n" +

                                            "BEGIN\r\n" +

                                            "CREATE TABLE [dbo].[ObjectAttributes](\r\n" +

                                            "	[RunKey] [int] NOT NULL,\r\n" +

                                            "	[ObjectKey] [int] NOT NULL,\r\n" +

                                            "	[ObjectAttrName] [nvarchar](1000) NOT NULL,\r\n" +

                                            "	[ObjectAttrValue] [nvarchar](max) NOT NULL\r\n" +

                                            ") ON [PRIMARY]\r\n" +

                                            "END\r\n" +

                                            "";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectAttributes_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectAttributes]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectAttributes]  WITH CHECK ADD  CONSTRAINT [FK_ObjectAttributes_Objects] FOREIGN KEY([RunKey], [ObjectKey])\r\n" +

                                            "REFERENCES [dbo].[Objects] ([RunKey], [ObjectKey])\r\n" +

                                            "IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectAttributes_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectAttributes]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectAttributes] CHECK CONSTRAINT [FK_ObjectAttributes_Objects]\r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectDependencies]  WITH CHECK ADD  CONSTRAINT [FK_ObjectDependencies_Objects] FOREIGN KEY([RunKey], [SrcObjectKey])\r\n" +

                                            "REFERENCES [dbo].[Objects] ([RunKey], [ObjectKey])\r\n" +

                                            "IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectDependencies] CHECK CONSTRAINT [FK_ObjectDependencies_Objects]\r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectDependencies]  WITH CHECK ADD  CONSTRAINT [FK_ObjectDependencies_Objects1] FOREIGN KEY([RunKey], [TgtObjectKey])\r\n" +

                                            "REFERENCES [dbo].[Objects] ([RunKey], [ObjectKey])\r\n" +

                                            "IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ObjectDependencies_Objects1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ObjectDependencies]'))\r\n" +

                                            "ALTER TABLE [dbo].[ObjectDependencies] CHECK CONSTRAINT [FK_ObjectDependencies_Objects1]\r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_RunScan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objects]'))\r\n" +

                                            "ALTER TABLE [dbo].[Objects]  WITH CHECK ADD  CONSTRAINT [FK_Objects_RunScan] FOREIGN KEY([RunKey])\r\n" +

                                            "REFERENCES [dbo].[RunScan] ([RunKey])\r\n" +

                                            "IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_RunScan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objects]'))\r\n" +

                                            "ALTER TABLE [dbo].[Objects] CHECK CONSTRAINT [FK_Objects_RunScan]\r\n" +

                                            "";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Connections]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[Connections]\r\n" +

                                            "AS SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[SourceTables]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[SourceTables]\r\n" +

                                            "AS SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ObjectRelationships]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ObjectRelationships]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[LineageMap]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[LineageMap]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[TargetTables]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[TargetTables]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[DataFlows]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[DataFlows]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[WalkSources]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[WalkSources]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Packages]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[Packages]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vAudit]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vAudit]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[TableLineageMap]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[TableLineageMap]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n" +

                                            "IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ConnectionsMapping]'))\r\n" +

                                            "EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ConnectionsMapping]\r\n" +

                                            "AS\r\n" +

                                            "SELECT 1 AS Column1' \r\n";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[TargetTables]\r\n" +

                                            "AS\r\n" +

                                            "SELECT\r\n" +

                                            "    Objects.RunKey,\r\n" +

                                            "    ObjectDependencies.DependencyType,\r\n" +

                                            "    Objects.ObjectKey,\r\n" +

                                            "    Objects.ObjectName,\r\n" +

                                            "    Objects.ObjectDesc,\r\n" +

                                            "    ObjectDependencies.SrcObjectKey AS TgtComponentKey,\r\n" +

                                            "    TargetObjects.ObjectName AS TargetComponentName,\r\n" +

                                            "    TargetObjects.ObjectDesc AS TargetComponentDesc,\r\n" +

                                            "    OD_DataFlow.SrcObjectKey AS DataFlowID,\r\n" +

                                            "    OD_DestConnection.SrcObjectKey AS DestinationConnectionID\r\n" +

                                            "FROM dbo.Objects\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies AS ObjectDependencies\r\n" +

                                            "        ON Objects.ObjectKey = ObjectDependencies.TgtObjectKey\r\n" +

                                            "       AND Objects.RunKey = ObjectDependencies.RunKey\r\n" +

                                            "INNER JOIN dbo.Objects AS TargetObjects\r\n" +

                                            "        ON ObjectDependencies.SrcObjectKey = TargetObjects.ObjectKey\r\n" +

                                            "       AND Objects.RunKey = TargetObjects.RunKey\r\n" +

                                            "       AND ObjectDependencies.RunKey = TargetObjects.RunKey\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies AS OD_DataFlow\r\n" +

                                            "        ON ObjectDependencies.SrcObjectKey = OD_DataFlow.TgtObjectKey\r\n" +

                                            "       AND ObjectDependencies.RunKey = OD_DataFlow.RunKey\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies AS OD_DestConnection\r\n" +

                                            "        ON Objects.ObjectKey = OD_DestConnection.TgtObjectKey\r\n" +

                                            "       AND Objects.RunKey = OD_DestConnection.RunKey\r\n" +

                                            "WHERE ObjectDependencies.DependencyType = N'Map'\r\n" +

                                            "  AND Objects.ObjectTypeString = N'Table'\r\n" +

                                            "  AND OD_DataFlow.DependencyType = N'Containment'\r\n" +

                                            "  AND OD_DestConnection.DependencyType = N'Containment'\r\n";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[SourceTables]\r\n" +

                                            "AS\r\n" +

                                            "SELECT\r\n" +

                                            "    dbo.Objects.RunKey,\r\n" +

                                            "    dbo.Objects.ObjectKey,\r\n" +

                                            "    dbo.Objects.ObjectName,\r\n" +

                                            "    dbo.Objects.ObjectTypeString,\r\n" +

                                            "    dbo.Objects.ObjectDesc,\r\n" +

                                            "    dbo.ObjectDependencies.TgtObjectKey AS SrcComponentKey,\r\n" +

                                            "    SourceObjects.ObjectName AS SourceObjectsName,\r\n" +

                                            "    SourceObjects.ObjectDesc AS SourceObjectsDesc,\r\n" +

                                            "    OD_DataFlow.SrcObjectKey AS DataFlowID,\r\n" +

                                            "    OD_DestConnection.SrcObjectKey AS SourceConnectionID\r\n" +

                                            "FROM dbo.Objects\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies\r\n" +

                                            "        ON dbo.Objects.ObjectKey = dbo.ObjectDependencies.SrcObjectKey\r\n" +

                                            "        AND dbo.Objects.RunKey = dbo.ObjectDependencies.RunKey\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies AS OD_DataFlow\r\n" +

                                            "        ON dbo.ObjectDependencies.TgtObjectKey = OD_DataFlow.TgtObjectKey\r\n" +

                                            "        AND dbo.ObjectDependencies.RunKey = OD_DataFlow.RunKey\r\n" +

                                            "INNER JOIN dbo.Objects AS SourceObjects\r\n" +

                                            "        ON dbo.ObjectDependencies.TgtObjectKey = SourceObjects.ObjectKey\r\n" +

                                            "        AND dbo.ObjectDependencies.RunKey = SourceObjects.RunKey\r\n" +

                                            "INNER JOIN dbo.ObjectDependencies AS OD_DestConnection\r\n" +

                                            "        ON dbo.Objects.ObjectKey = OD_DestConnection.TgtObjectKey\r\n" +

                                            "        AND dbo.Objects.RunKey = OD_DestConnection.RunKey\r\n" +

                                            "WHERE dbo.ObjectDependencies.DependencyType = N'Map'\r\n" +

                                            "  AND dbo.Objects.ObjectTypeString = N'Table'\r\n" +

                                            "  AND OD_DataFlow.DependencyType = N'Containment'\r\n" +

                                            "  AND OD_DataFlow.DependencyType = OD_DestConnection.DependencyType";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[LineageMap]\r\n" +

                                            "AS\r\n" +

                                            "SELECT\r\n" +

                                            "	RunKey,\r\n" +

                                            "	SrcObjectKey,\r\n" +

                                            "	TgtObjectKey\r\n" +

                                            "FROM dbo.ObjectDependencies\r\n" +

                                            "WHERE DependencyType = N'Map'";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[WalkSources]\r\n" +

                                            "AS\r\n" +

                                            "WITH f(RunKey, osrc, tgt, lvl, objecttype) \r\n" +

                                            "AS \r\n" +

                                            "(SELECT  Objects.RunKey,    dbo.SourceTables.ObjectKey\r\n" +

                                            "	, dbo.SourceTables.SrcComponentKey\r\n" +

                                            "	, 0 AS Expr1\r\n" +

                                            "	, dbo.Objects.ObjectTypeString\r\n" +

                                            "FROM dbo.SourceTables \r\n" +

                                            "INNER JOIN dbo.Objects \r\n" +

                                            "	ON dbo.SourceTables.ObjectKey = dbo.Objects.ObjectKey\r\n" +

                                            "	AND SourceTables.RunKey = Objects.RunKey\r\n" +

                                            "UNION ALL\r\n" +

                                            "SELECT  Objects_1.RunKey,    f_2.osrc\r\n" +

                                            "	, dbo.LineageMap.TgtObjectKey\r\n" +

                                            "	, f_2.lvl + 1 AS Expr1\r\n" +

                                            "	, Objects_1.ObjectTypeString\r\n" +

                                            "FROM         f AS f_2 \r\n" +

                                            "INNER JOIN dbo.LineageMap \r\n" +

                                            "	ON f_2.tgt = dbo.LineageMap.SrcObjectKey \r\n" +

                                            "INNER JOIN dbo.Objects AS Objects_1 \r\n" +

                                            "	ON dbo.LineageMap.TgtObjectKey = Objects_1.ObjectKey\r\n" +

                                            "	AND LineageMap.RunKey = Objects_1.RunKey\r\n" +

                                            "WHERE     (NOT (f_2.osrc = f_2.tgt)))\r\n" +

                                            "SELECT   RunKey,   osrc, tgt, lvl, objecttype\r\n" +

                                            "FROM         f AS f_1";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[ObjectRelationships]\r\n" +

                                            "AS\r\n" +

                                            "SELECT\r\n" +

                                            "    RunKey,\r\n" +

                                            "    SrcObjectKey AS ParentObjectKey,\r\n" +

                                            "    TgtObjectKey AS ChildObjectKey\r\n" +

                                            "FROM dbo.ObjectDependencies\r\n" +

                                            "WHERE DependencyType = N'Containment'";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[Packages]\r\n" +

                                            "AS\r\n" +

                                            "SELECT \r\n" +

                                            "	Objects.RunKey,\r\n" +

                                            "	Objects.ObjectKey AS PackageID, \r\n" +

                                            "	Objects.ObjectName AS PackageName,\r\n" +

                                            "	Objects.ObjectDesc AS PackageDesc,\r\n" +

                                            "	PackageProperties.PackageLocation,\r\n" +

                                            "	PackageProperties.PackageGUID\r\n" +

                                            "FROM [dbo].[Objects],\r\n" +

                                            "	(SELECT \r\n" +

                                            "		RunKey,\r\n" +

                                            "		PackageProperties.ObjectKey,\r\n" +

                                            "		[PackageLocation],\r\n" +

                                            "		[PackageGUID]\r\n" +

                                            "	FROM dbo.ObjectAttributes \r\n" +

                                            "	PIVOT (\r\n" +

                                            "		MIN (ObjectAttrValue) \r\n" +

                                            "		FOR ObjectAttrName \r\n" +

                                            "		IN ([PackageLocation], [PackageGUID])\r\n" +

                                            "		) AS PackageProperties\r\n" +

                                            "	) AS PackageProperties\r\n" +

                                            "WHERE [Objects].ObjectKey = PackageProperties.ObjectKey \r\n" +

                                            "AND [Objects].RunKey = PackageProperties.RunKey\r\n" +

                                            "AND [Objects].ObjectTypeString = N'SSIS Package'";

                    sqlCommand.ExecuteNonQuery();

                    sqlCommand.CommandText = "ALTER VIEW [dbo].[Connections]\r\n" +

                                            "AS\r\n" +

                                            "SELECT \r\n" +

                                            "	[Objects].[RunKey],\r\n" +

                                            "	[Objects].ObjectKey AS ConnectionID,\r\n" +

                                            "	[Objects].ObjectName AS ConnectionName,\r\n" +

                                            "	[Objects].ObjectDesc AS ConnectionDesc,\r\n" +

                                            "	ConnectionString,\r\n" +

                                            "	ConnectionProperties.[Server],\r\n" +

                                            "	ConnectionProperties.[Database]\r\n" +

                                            "FROM [dbo].[Objects] \r\n" +

                                            "INNER JOIN\r\n" +

                                            "	(SELECT\r\n" +

                                            "		RunKey,\r\n" +

                                            "		ConnectionProperties.ObjectKey,\r\n" +

                                         …

Large files files are truncated, but you can click here to view the full file