public void TestScript()
{
var f = new Routine("dbo", "udf_GetDate");
f.Text = @"
CREATE FUNCTION [dbo].[udf_GetDate]()
RETURNS DATETIME AS
BEGIN
RETURN GETDATE()
END
";
Console.WriteLine(f.ScriptCreate(null));
TestHelper.ExecBatchSql(f.ScriptCreate(null) + "\nGO", "");
TestHelper.ExecSql("drop function [dbo].[udf_GetDate]", "");
}
public void TestScript()
{
var t = new Table("dbo", "Address");
t.Columns.Add(new Column("id", "int", false, null));
t.Columns.Add(new Column("street", "varchar", 50, false, null));
t.Columns.Add(new Column("city", "varchar", 50, false, null));
t.Columns.Add(new Column("state", "char", 2, false, null));
t.Columns.Add(new Column("zip", "char", 5, false, null));
t.Constraints.Add(new Constraint("PK_Address", "PRIMARY KEY", "id"));
var getAddress = new Routine("dbo", "GetAddress");
getAddress.Text = @"
CREATE PROCEDURE [dbo].[GetAddress]
@id int
AS
select * from Address where id = @id
";
TestHelper.ExecSql(t.ScriptCreate(), "");
TestHelper.ExecBatchSql(getAddress.ScriptCreate(null) + "\nGO", "");
TestHelper.ExecSql("drop table [dbo].[Address]", "");
TestHelper.ExecSql("drop procedure [dbo].[GetAddress]", "");
}
public void Load()
{
var cnStrBuilder = new SqlConnectionStringBuilder(Connection);
Tables.Clear();
Routines.Clear();
ForeignKeys.Clear();
DataTables.Clear();
using (var cn = new SqlConnection(Connection))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
// query schema for database properties
cm.CommandText = @"
select
[compatibility_level],
[collation_name],
[is_auto_close_on],
[is_auto_shrink_on],
[snapshot_isolation_state],
[is_read_committed_snapshot_on],
[recovery_model_desc],
[page_verify_option_desc],
[is_auto_create_stats_on],
[is_auto_update_stats_on],
[is_auto_update_stats_async_on],
[is_ansi_null_default_on],
[is_ansi_nulls_on],
[is_ansi_padding_on],
[is_ansi_warnings_on],
[is_arithabort_on],
[is_concat_null_yields_null_on],
[is_numeric_roundabort_on],
[is_quoted_identifier_on],
[is_recursive_triggers_on],
[is_cursor_close_on_commit_on],
[is_local_cursor_default],
[is_trustworthy_on],
[is_db_chaining_on],
[is_parameterization_forced],
[is_date_correlation_on]
from sys.databases
where name = @dbname
";
cm.Parameters.AddWithValue("@dbname", cnStrBuilder.InitialCatalog);
using (IDataReader dr = cm.ExecuteReader())
{
if (dr.Read())
{
SetPropString("COMPATIBILITY_LEVEL", dr["compatibility_level"]);
SetPropString("COLLATE", dr["collation_name"]);
SetPropOnOff("AUTO_CLOSE", dr["is_auto_close_on"]);
SetPropOnOff("AUTO_SHRINK", dr["is_auto_shrink_on"]);
if (dr["snapshot_isolation_state"] != DBNull.Value)
{
FindProp("ALLOW_SNAPSHOT_ISOLATION").Value =
(byte)dr["snapshot_isolation_state"] == 0 ||
(byte)dr["snapshot_isolation_state"] == 2
? "OFF"
: "ON";
}
SetPropOnOff("READ_COMMITTED_SNAPSHOT", dr["is_read_committed_snapshot_on"]);
SetPropString("RECOVERY", dr["recovery_model_desc"]);
SetPropString("PAGE_VERIFY", dr["page_verify_option_desc"]);
SetPropOnOff("AUTO_CREATE_STATISTICS", dr["is_auto_create_stats_on"]);
SetPropOnOff("AUTO_UPDATE_STATISTICS", dr["is_auto_update_stats_on"]);
SetPropOnOff("AUTO_UPDATE_STATISTICS_ASYNC", dr["is_auto_update_stats_async_on"]);
SetPropOnOff("ANSI_NULL_DEFAULT", dr["is_ansi_null_default_on"]);
SetPropOnOff("ANSI_NULLS", dr["is_ansi_nulls_on"]);
SetPropOnOff("ANSI_PADDING", dr["is_ansi_padding_on"]);
SetPropOnOff("ANSI_WARNINGS", dr["is_ansi_warnings_on"]);
SetPropOnOff("ARITHABORT", dr["is_arithabort_on"]);
SetPropOnOff("CONCAT_NULL_YIELDS_NULL", dr["is_concat_null_yields_null_on"]);
SetPropOnOff("NUMERIC_ROUNDABORT", dr["is_numeric_roundabort_on"]);
SetPropOnOff("QUOTED_IDENTIFIER", dr["is_quoted_identifier_on"]);
SetPropOnOff("RECURSIVE_TRIGGERS", dr["is_recursive_triggers_on"]);
SetPropOnOff("CURSOR_CLOSE_ON_COMMIT", dr["is_cursor_close_on_commit_on"]);
if (dr["is_local_cursor_default"] != DBNull.Value)
{
FindProp("CURSOR_DEFAULT").Value =
(bool)dr["is_local_cursor_default"] ? "LOCAL" : "GLOBAL";
}
SetPropOnOff("TRUSTWORTHY", dr["is_trustworthy_on"]);
SetPropOnOff("DB_CHAINING", dr["is_db_chaining_on"]);
if (dr["is_parameterization_forced"] != DBNull.Value)
{
FindProp("PARAMETERIZATION").Value =
(bool)dr["is_parameterization_forced"] ? "FORCED" : "SIMPLE";
}
SetPropOnOff("DATE_CORRELATION_OPTIMIZATION", dr["is_date_correlation_on"]);
}
}
//get schemas
cm.CommandText = @"
select s.name as schemaName, p.name as principalName
from sys.schemas s
inner join sys.database_principals p on s.principal_id = p.principal_id
where s.schema_id < 16384
and s.name not in ('dbo','guest','sys','INFORMATION_SCHEMA')
order by schema_id
";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Schemas.Add(new Schema((string)dr["schemaName"], (string)dr["principalName"]));
}
}
//get tables
cm.CommandText = @"
select
TABLE_SCHEMA,
TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Tables.Add(new Table((string)dr["TABLE_SCHEMA"], (string)dr["TABLE_NAME"]));
}
}
//get columns
cm.CommandText = @"
select
t.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
c.IS_NULLABLE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_SCALE
from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t
on t.TABLE_NAME = c.TABLE_NAME
and t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_CATALOG = c.TABLE_CATALOG
where
t.TABLE_TYPE = 'BASE TABLE'
";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
var c = new Column();
c.Name = (string)dr["COLUMN_NAME"];
c.Type = (string)dr["DATA_TYPE"];
c.IsNullable = (string)dr["IS_NULLABLE"] == "YES";
switch (c.Type)
{
case "binary":
case "char":
case "nchar":
case "nvarchar":
case "varbinary":
case "varchar":
c.Length = (int)dr["CHARACTER_MAXIMUM_LENGTH"];
break;
case "decimal":
case "numeric":
c.Precision = (byte)dr["NUMERIC_PRECISION"];
c.Scale = (int)dr["NUMERIC_SCALE"];
break;
}
FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]).Columns.Add(c);
}
}
//get column identities
cm.CommandText = @"
select
s.name as TABLE_SCHEMA,
t.name as TABLE_NAME,
c.name AS COLUMN_NAME,
i.SEED_VALUE, i.INCREMENT_VALUE
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.identity_columns i on i.object_id = c.object_id
and i.column_id = c.column_id
inner join sys.schemas s on s.schema_id = t.schema_id ";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
try
{
Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
Column c = t.Columns.Find((string)dr["COLUMN_NAME"]);
string seed = dr["SEED_VALUE"].ToString();
string increment = dr["INCREMENT_VALUE"].ToString();
c.Identity = new Identity(seed, increment);
}
catch (Exception ex)
{
throw new ApplicationException(string.Format("{0}.{1} : {2}", dr["TABLE_SCHEMA"], dr["TABLE_NAME"], ex.Message), ex);
}
}
}
//get column defaults
cm.CommandText = @"
select
s.name as TABLE_SCHEMA,
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
d.name as DEFAULT_NAME,
d.definition as DEFAULT_VALUE
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.default_constraints d on c.column_id = d.parent_column_id
and d.parent_object_id = c.object_id
inner join sys.schemas s on s.schema_id = t.schema_id";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
t.Columns.Find((string)dr["COLUMN_NAME"]).Default =
new Default((string)dr["DEFAULT_NAME"], (string)dr["DEFAULT_VALUE"]);
}
}
//get constraints & indexes
cm.CommandText = @"
select
s.name as schemaName,
t.name as tableName,
i.name as indexName,
c.name as columnName,
i.is_primary_key,
i.is_unique_constraint,
i.is_unique,
i.type_desc,
isnull(ic.is_included_column, 0) as is_included_column
from sys.tables t
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = t.object_id
and c.column_id = ic.column_id
inner join sys.schemas s on s.schema_id = t.schema_id
order by s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Table t = FindTable((string)dr["tableName"], (string)dr["schemaName"]);
Constraint c = t.FindConstraint((string)dr["indexName"]);
if (c == null)
{
c = new Constraint((string)dr["indexName"], "", "");
t.Constraints.Add(c);
c.Table = t;
}
c.Clustered = (string)dr["type_desc"] == "CLUSTERED";
c.Unique = (bool)dr["is_unique"];
if ((bool)dr["is_included_column"])
{
c.IncludedColumns.Add((string)dr["columnName"]);
}
else
{
c.Columns.Add((string)dr["columnName"]);
}
c.Type = "INDEX";
if ((bool)dr["is_primary_key"]) c.Type = "PRIMARY KEY";
if ((bool)dr["is_unique_constraint"]) c.Type = "UNIQUE";
}
}
//get foreign keys
cm.CommandText = @"
select
TABLE_SCHEMA,
TABLE_NAME,
CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
var fk = new ForeignKey((string)dr["CONSTRAINT_NAME"]);
fk.Table = t;
ForeignKeys.Add(fk);
}
}
//get foreign key props
cm.CommandText = @"
select
CONSTRAINT_NAME,
UPDATE_RULE,
DELETE_RULE,
fk.is_disabled
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
ForeignKey fk = FindForeignKey((string)dr["CONSTRAINT_NAME"]);
fk.OnUpdate = (string)dr["UPDATE_RULE"];
fk.OnDelete = (string)dr["DELETE_RULE"];
fk.Check = !(bool)dr["is_disabled"];
}
}
//get foreign key columns and ref table
cm.CommandText = @"
select
fk.name as CONSTRAINT_NAME,
c1.name as COLUMN_NAME,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA,
OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME,
c2.name as REF_COLUMN_NAME
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
inner join sys.columns c1
on fkc.parent_column_id = c1.column_id
and fkc.parent_object_id = c1.object_id
inner join sys.columns c2
on fkc.referenced_column_id = c2.column_id
and fkc.referenced_object_id = c2.object_id
order by fk.name
";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
ForeignKey fk = FindForeignKey((string)dr["CONSTRAINT_NAME"]);
if (fk == null)
{
continue;
}
fk.Columns.Add((string)dr["COLUMN_NAME"]);
fk.RefColumns.Add((string)dr["REF_COLUMN_NAME"]);
if (fk.RefTable == null)
{
fk.RefTable = FindTable((string)dr["REF_TABLE_NAME"], (string)dr["REF_TABLE_SCHEMA"]);
}
}
}
//get routines
cm.CommandText = @"
select
s.name as schemaName,
o.name as routineName,
o.type_desc,
m.definition,
m.uses_ansi_nulls,
m.uses_quoted_identifier,
t.name as tableName
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
left join sys.triggers tr on m.object_id = tr.object_id
left join sys.tables t on tr.parent_id = t.object_id";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]);
r.Text = (string)dr["definition"];
r.AnsiNull = (bool)dr["uses_ansi_nulls"];
r.QuotedId = (bool)dr["uses_quoted_identifier"];
Routines.Add(r);
switch ((string)dr["type_desc"])
{
case "SQL_STORED_PROCEDURE":
r.Type = "PROCEDURE";
break;
case "SQL_TRIGGER":
r.Type = "TRIGGER";
break;
case "SQL_SCALAR_FUNCTION":
r.Type = "FUNCTION";
break;
case "VIEW":
r.Type = "VIEW";
break;
}
}
}
}
}
}
private static string MakeFileName(Routine r)
{
return MakeFileName(r.Schema, r.Name);
}
public void Load()
{
var cnStrBuilder = new SqlConnectionStringBuilder(Connection);
Tables.Clear();
Routines.Clear();
ForeignKeys.Clear();
DataTables.Clear();
using (var cn = new SqlConnection(Connection)) {
cn.Open();
using (SqlCommand cm = cn.CreateCommand()) {
// query schema for database properties
cm.CommandText = @"
select
[compatibility_level],
[collation_name],
[is_auto_close_on],
[is_auto_shrink_on],
[snapshot_isolation_state],
[is_read_committed_snapshot_on],
[recovery_model_desc],
[page_verify_option_desc],
[is_auto_create_stats_on],
[is_auto_update_stats_on],
[is_auto_update_stats_async_on],
[is_ansi_null_default_on],
[is_ansi_nulls_on],
[is_ansi_padding_on],
[is_ansi_warnings_on],
[is_arithabort_on],
[is_concat_null_yields_null_on],
[is_numeric_roundabort_on],
[is_quoted_identifier_on],
[is_recursive_triggers_on],
[is_cursor_close_on_commit_on],
[is_local_cursor_default],
[is_trustworthy_on],
[is_db_chaining_on],
[is_parameterization_forced],
[is_date_correlation_on]
from sys.databases
where name = @dbname
";
cm.Parameters.AddWithValue("@dbname", cnStrBuilder.InitialCatalog);
using (IDataReader dr = cm.ExecuteReader()) {
if (dr.Read()) {
SetPropString("COMPATIBILITY_LEVEL", dr["compatibility_level"]);
SetPropString("COLLATE", dr["collation_name"]);
SetPropOnOff("AUTO_CLOSE", dr["is_auto_close_on"]);
SetPropOnOff("AUTO_SHRINK", dr["is_auto_shrink_on"]);
if (dr["snapshot_isolation_state"] != DBNull.Value) {
FindProp("ALLOW_SNAPSHOT_ISOLATION").Value = (byte) dr["snapshot_isolation_state"] == 0 ||
(byte) dr["snapshot_isolation_state"] == 2
? "OFF"
: "ON";
}
SetPropOnOff("READ_COMMITTED_SNAPSHOT", dr["is_read_committed_snapshot_on"]);
SetPropString("RECOVERY", dr["recovery_model_desc"]);
SetPropString("PAGE_VERIFY", dr["page_verify_option_desc"]);
SetPropOnOff("AUTO_CREATE_STATISTICS", dr["is_auto_create_stats_on"]);
SetPropOnOff("AUTO_UPDATE_STATISTICS", dr["is_auto_update_stats_on"]);
SetPropOnOff("AUTO_UPDATE_STATISTICS_ASYNC", dr["is_auto_update_stats_async_on"]);
SetPropOnOff("ANSI_NULL_DEFAULT", dr["is_ansi_null_default_on"]);
SetPropOnOff("ANSI_NULLS", dr["is_ansi_nulls_on"]);
SetPropOnOff("ANSI_PADDING", dr["is_ansi_padding_on"]);
SetPropOnOff("ANSI_WARNINGS", dr["is_ansi_warnings_on"]);
SetPropOnOff("ARITHABORT", dr["is_arithabort_on"]);
SetPropOnOff("CONCAT_NULL_YIELDS_NULL", dr["is_concat_null_yields_null_on"]);
SetPropOnOff("NUMERIC_ROUNDABORT", dr["is_numeric_roundabort_on"]);
SetPropOnOff("QUOTED_IDENTIFIER", dr["is_quoted_identifier_on"]);
SetPropOnOff("RECURSIVE_TRIGGERS", dr["is_recursive_triggers_on"]);
SetPropOnOff("CURSOR_CLOSE_ON_COMMIT", dr["is_cursor_close_on_commit_on"]);
if (dr["is_local_cursor_default"] != DBNull.Value) {
FindProp("CURSOR_DEFAULT").Value = (bool) dr["is_local_cursor_default"] ? "LOCAL" : "GLOBAL";
}
SetPropOnOff("TRUSTWORTHY", dr["is_trustworthy_on"]);
SetPropOnOff("DB_CHAINING", dr["is_db_chaining_on"]);
if (dr["is_parameterization_forced"] != DBNull.Value) {
FindProp("PARAMETERIZATION").Value = (bool) dr["is_parameterization_forced"] ? "FORCED" : "SIMPLE";
}
SetPropOnOff("DATE_CORRELATION_OPTIMIZATION", dr["is_date_correlation_on"]);
}
}
//get schemas
cm.CommandText = @"
select s.name as schemaName, p.name as principalName
from sys.schemas s
inner join sys.database_principals p on s.principal_id = p.principal_id
where s.schema_id < 16384
and s.name not in ('dbo','guest','sys','INFORMATION_SCHEMA')
order by schema_id
";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
Schemas.Add(new Schema((string) dr["schemaName"], (string) dr["principalName"]));
}
}
//get tables
cm.CommandText = @"
select
TABLE_SCHEMA,
TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
Tables.Add(new Table((string) dr["TABLE_SCHEMA"], (string) dr["TABLE_NAME"]));
}
}
//get columns
cm.CommandText = @"
select
t.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
c.IS_NULLABLE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_SCALE
from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t
on t.TABLE_NAME = c.TABLE_NAME
and t.TABLE_SCHEMA = c.TABLE_SCHEMA
and t.TABLE_CATALOG = c.TABLE_CATALOG
where
t.TABLE_TYPE = 'BASE TABLE'
";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
var c = new Column();
c.Name = (string) dr["COLUMN_NAME"];
c.Type = (string) dr["DATA_TYPE"];
c.IsNullable = (string) dr["IS_NULLABLE"] == "YES";
switch (c.Type) {
case "binary":
case "char":
case "nchar":
case "nvarchar":
case "varbinary":
case "varchar":
c.Length = (int) dr["CHARACTER_MAXIMUM_LENGTH"];
break;
case "decimal":
case "numeric":
c.Precision = (byte) dr["NUMERIC_PRECISION"];
c.Scale = (int) dr["NUMERIC_SCALE"];
break;
}
FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]).Columns.Add(c);
}
}
//get column identities
cm.CommandText = @"
select
s.name as TABLE_SCHEMA,
t.name as TABLE_NAME,
c.name AS COLUMN_NAME,
i.SEED_VALUE, i.INCREMENT_VALUE
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.identity_columns i on i.object_id = c.object_id
and i.column_id = c.column_id
inner join sys.schemas s on s.schema_id = t.schema_id ";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
try {
Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
Column c = t.Columns.Find((string) dr["COLUMN_NAME"]);
string seed = dr["SEED_VALUE"].ToString();
string increment = dr["INCREMENT_VALUE"].ToString();
c.Identity = new Identity(seed, increment);
} catch (Exception ex) {
throw new ApplicationException(
string.Format("{0}.{1} : {2}", dr["TABLE_SCHEMA"], dr["TABLE_NAME"], ex.Message), ex);
}
}
}
//get column defaults
cm.CommandText = @"
select
s.name as TABLE_SCHEMA,
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
d.name as DEFAULT_NAME,
d.definition as DEFAULT_VALUE
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.default_constraints d on c.column_id = d.parent_column_id
and d.parent_object_id = c.object_id
inner join sys.schemas s on s.schema_id = t.schema_id";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
t.Columns.Find((string) dr["COLUMN_NAME"]).Default = new Default((string) dr["DEFAULT_NAME"],
(string) dr["DEFAULT_VALUE"]);
}
}
//get constraints & indexes
cm.CommandText = @"
select
s.name as schemaName,
t.name as tableName,
t.baseType,
i.name as indexName,
c.name as columnName,
i.is_primary_key,
i.is_unique_constraint,
i.is_unique,
i.type_desc,
isnull(ic.is_included_column, 0) as is_included_column
from (
select object_id, name, schema_id, 'T' as baseType
from sys.tables
union
select object_id, name, schema_id, 'V' as baseType
from sys.views
) t
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = t.object_id
and c.column_id = ic.column_id
inner join sys.schemas s on s.schema_id = t.schema_id
order by s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
Table t = (string) dr["baseType"] == "V"
? new Table((string) dr["schemaName"], (string) dr["tableName"])
: FindTable((string) dr["tableName"], (string) dr["schemaName"]);
Constraint c = t.FindConstraint((string) dr["indexName"]);
if (c == null) {
c = new Constraint((string) dr["indexName"], "", "");
t.Constraints.Add(c);
c.Table = t;
if ((string) dr["baseType"] == "V")
ViewIndexes.Add(c);
}
c.Clustered = (string) dr["type_desc"] == "CLUSTERED";
c.Unique = (bool) dr["is_unique"];
if ((bool) dr["is_included_column"]) {
c.IncludedColumns.Add((string) dr["columnName"]);
} else {
c.Columns.Add((string) dr["columnName"]);
}
c.Type = "INDEX";
if ((bool) dr["is_primary_key"])
c.Type = "PRIMARY KEY";
if ((bool) dr["is_unique_constraint"])
c.Type = "UNIQUE";
}
}
//get foreign keys
cm.CommandText = @"
select
TABLE_SCHEMA,
TABLE_NAME,
CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
var fk = new ForeignKey((string) dr["CONSTRAINT_NAME"]);
fk.Table = t;
ForeignKeys.Add(fk);
}
}
//get foreign key props
cm.CommandText = @"
select
CONSTRAINT_NAME,
UPDATE_RULE,
DELETE_RULE,
fk.is_disabled
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]);
fk.OnUpdate = (string) dr["UPDATE_RULE"];
fk.OnDelete = (string) dr["DELETE_RULE"];
fk.Check = !(bool) dr["is_disabled"];
}
}
//get foreign key columns and ref table
cm.CommandText = @"
select
fk.name as CONSTRAINT_NAME,
c1.name as COLUMN_NAME,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA,
OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME,
c2.name as REF_COLUMN_NAME
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
inner join sys.columns c1
on fkc.parent_column_id = c1.column_id
and fkc.parent_object_id = c1.object_id
inner join sys.columns c2
on fkc.referenced_column_id = c2.column_id
and fkc.referenced_object_id = c2.object_id
order by fk.name, fkc.constraint_column_id
";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]);
if (fk == null) {
continue;
}
fk.Columns.Add((string) dr["COLUMN_NAME"]);
fk.RefColumns.Add((string) dr["REF_COLUMN_NAME"]);
if (fk.RefTable == null) {
fk.RefTable = FindTable((string) dr["REF_TABLE_NAME"], (string) dr["REF_TABLE_SCHEMA"]);
}
}
}
//get routines
cm.CommandText = @"
select
s.name as schemaName,
o.name as routineName,
o.type_desc,
m.definition,
m.uses_ansi_nulls,
m.uses_quoted_identifier,
t.name as tableName
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
left join sys.triggers tr on m.object_id = tr.object_id
left join sys.tables t on tr.parent_id = t.object_id";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
if (dr["definition"] is DBNull) {
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine("Warning: Unable to get definition for {0} {1}.{2}", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]);
Console.ForegroundColor = ConsoleColor.White;
} else {
if (!((string)dr["definition"]).Contains((string)dr["routineName"])) {
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine("Warning: {0} {1}.{2} has been renamed since it's definition.", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]);
Console.ForegroundColor = ConsoleColor.White;
}
var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]);
r.Text = (string)dr["definition"];
r.AnsiNull = (bool)dr["uses_ansi_nulls"];
r.QuotedId = (bool)dr["uses_quoted_identifier"];
Routines.Add(r);
switch ((string)dr["type_desc"]) {
case "SQL_STORED_PROCEDURE":
r.RoutineType = Routine.RoutineKind.Procedure;
break;
case "SQL_TRIGGER":
r.RoutineType = Routine.RoutineKind.Trigger;
break;
case "SQL_SCALAR_FUNCTION":
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
r.RoutineType = Routine.RoutineKind.Function;
break;
case "VIEW":
r.RoutineType = Routine.RoutineKind.View;
break;
}
}
}
}
// get xml schemas
cm.CommandText =
@"select s.name as DBSchemaName, x.name as XMLSchemaCollectionName, xml_schema_namespace(s.name, x.name) as definition
from sys.xml_schema_collections x
inner join sys.schemas s on s.schema_id = x.schema_id
where s.name != 'sys'";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
var r = new Routine((string) dr["DBSchemaName"], (string) dr["XMLSchemaCollectionName"]) {
Text =
string.Format("CREATE XML SCHEMA COLLECTION {0}.{1} AS N'{2}'", dr["DBSchemaName"],
dr["XMLSchemaCollectionName"], dr["definition"]),
RoutineType = Routine.RoutineKind.XmlSchemaCollection
};
Routines.Add(r);
}
}
// get CLR assemblies
cm.CommandText = @"select a.name as AssemblyName, a.permission_set_desc, af.name as FileName, af.content
from sys.assemblies a
inner join sys.assembly_files af on a.assembly_id = af.assembly_id
where a.is_user_defined = 1
order by a.name, af.file_id";
SqlAssembly a = null;
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
if (a == null || a.Name != (string) dr["AssemblyName"])
a = new SqlAssembly((string) dr["permission_set_desc"], (string) dr["AssemblyName"]);
a.Files.Add(new KeyValuePair<string, byte[]>((string) dr["FileName"], (byte[]) dr["content"]));
if (!Assemblies.Contains(a))
Assemblies.Add(a);
}
}
// get users that have access to the database
cm.CommandText =
@"select dp.name as UserName, USER_NAME(drm.role_principal_id) as AssociatedDBRole, default_schema_name
from sys.database_principals dp
left outer join sys.database_role_members drm on dp.principal_id = drm.member_principal_id
where dp.type_desc = 'SQL_USER'
and dp.sid not in (0x00, 0x01) --ignore guest and dbo
and dp.is_fixed_role = 0
order by dp.name";
SqlUser u = null;
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
if (u == null || u.Name != (string) dr["UserName"])
u = new SqlUser((string) dr["UserName"], (string) dr["default_schema_name"]);
if (!(dr["AssociatedDBRole"] is DBNull))
u.DatabaseRoles.Add((string) dr["AssociatedDBRole"]);
if (!Users.Contains(u))
Users.Add(u);
}
}
// get sql logins
cm.CommandText = @"select sp.name, sl.password_hash
from sys.server_principals sp
inner join sys.sql_logins sl on sp.principal_id = sl.principal_id and sp.type_desc = 'SQL_LOGIN'
where sp.name not like '##%##'
and sp.name != 'SA'
order by sp.name";
using (SqlDataReader dr = cm.ExecuteReader()) {
while (dr.Read()) {
u = Users.SingleOrDefault(user => user.Name == (string) dr["name"]);
if (u != null && !(dr["password_hash"] is DBNull))
u.PasswordHash = (byte[]) dr["password_hash"];
}
}
}
}
}
