public SqlDeploy(SqlConnectionConfiguration deployConfig, IEnumerable <IDbObject> deployObjects)
{
_config = deployConfig;
_deployObjects = deployObjects;
_transconn = new SqlConnection(_config.ConnectionString);
_conn = new SqlConnection(_config.ConnectionString);
}
public SqlDependencyChecker(SqlConnectionConfiguration connectionConfig)
{
_connConfig = connectionConfig;
_conn = new SqlConnection(_connConfig.ConnectionString);
try
{
_conn.Open();
Debug.WriteLine("Connected to Sql Server");
}
catch (Exception ex)
{
Debug.WriteLine("Failed to connect to the Sql Server");
Debug.WriteLine(ex.Message);
}
_dependencySQL = @"WITH cteDeps AS (
SELECT
0 AS [Level]
,SCHEMA_NAME(o.schema_id) AS [Schema]
,o.name AS [ObjectName]
,OBJECT_ID(SCHEMA_NAME(o.schema_id) + '.' + o.name) AS [ObjectId]
,o.type AS [ObjectType]
,o.type_desc AS [ObjectTypeDesc]
FROM
sys.objects AS o
WHERE
o.name = '@Object' AND o.schema_id = SCHEMA_ID('@Schema')
UNION ALL
SELECT
PARENT.Level + 1 AS Level
,CHILD.referenced_schema_name AS [Schema]
,CHILD.referenced_entity_name AS [ObjectName]
,CHILD.referenced_id AS [ObjectId]
,COBJECT.type AS [ObjectType]
,COBJECT.type_desc [ObjectTypeDesc]
FROM
cteDeps AS PARENT
CROSS APPLY
sys.dm_sql_referenced_entities(PARENT.[Schema] + '.' + PARENT.ObjectName, 'OBJECT') AS CHILD
INNER JOIN
sys.objects AS COBJECT ON (COBJECT.object_id = referenced_id)
WHERE
COBJECT.type NOT IN ('SN')
)
SELECT
MAX(D.Level) DependencyLevel
,D.[Schema]
,D.ObjectName
,D.ObjectId
,D.ObjectType
,D.ObjectTypeDesc
FROM
cteDeps AS D
GROUP BY
D.[Schema]
,D.ObjectName
,D.ObjectId
,D.ObjectType
,D.ObjectTypeDesc
ORDER BY
DependencyLevel DESC" ;
}