/EFRepository/DependencyAnalyzer2008/Repository.cs
C# | 1951 lines | 1613 code | 147 blank | 191 comment | 117 complexity | e32d125f08761732e81b678d1580e9a9 MD5 | raw file
Possible License(s): CC-BY-SA-3.0
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
- {
-
- 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" +
- " ConnectionString,\r\n" +
- " [Server],\r\n" +
- " [Database]\r\n" +
- …
Large files files are truncated, but you can click here to view the full file