예제 #1
0
        public override void AddCode(SqlConnection connection, SqlObject obj)
        {
            SysIndex index = null;

            using (var cmd = new SqlCommand(@"SELECT ind.index_id AS IndexId
,ind.name AS IndexName
,col.name AS ColumnName
,t.name AS TableName
,s.name AS SchemaName
,ic.index_column_id AS ColumnId
,ind.is_unique AS IsUnique
,ind.type_desc as Type
,ind.has_filter AS HasFilter
,ind.filter_definition AS FilterDefinition
,ic.is_descending_key AS IsDescending
,ic.is_included_column AS IsIncluded
FROM sys.indexes ind 
JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
JOIN sys.tables t ON ind.object_id = t.object_id 
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ind.name = @NAME
  AND ind.object_id = @ID
ORDER BY TableName
,IndexId
,ColumnId", connection))
            {
                cmd.Parameters.Add("@NAME", SqlDbType.NVarChar, 128);
                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@NAME"].Value = obj.Name;
                cmd.Parameters["@ID"].Value   = obj.ObjectId;
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.HasRows)
                    {
                        return;
                    }
                    while (reader.Read())
                    {
                        if (index == null)
                        {
                            var tbl = new SysTable
                            {
                                Name   = reader[3].ToString(),
                                Schema = reader[4].ToString()
                            };

                            index = new SysIndex
                            {
                                Id               = Convert.ToInt32(reader[0]),
                                Name             = reader[1].ToString(),
                                Table            = tbl,
                                Type             = reader[7].ToString(),
                                IsUnique         = Convert.ToBoolean(reader[6]),
                                HasFilter        = Convert.ToBoolean(reader[8]),
                                FilterDefinition = Convert.ToString(reader[9])
                            };
                        }

                        index.Columns.Add(new SysIndexColumn
                        {
                            Id           = Convert.ToInt32(reader[5]),
                            Name         = Convert.ToString(reader[2]),
                            IsDescending = Convert.ToBoolean(reader[10]),
                            IsIncluded   = Convert.ToBoolean(reader[11]),
                        });
                    }
                    var buffer = new StringBuilder();
                    buffer.AppendLine(@"DECLARE @Name nvarchar(128), @TableName nvarchar(128), @TableSchema nvarchar(128)");
                    buffer.AppendFormat(
                        @"SELECT @Name = N'{0}', @TableName=N'{1}', @TableSchema = N'{2}'",
                        index.Name,
                        index.Table.Name,
                        index.Table.Schema);
                    buffer.AppendLine();
                    buffer.AppendLine(
                        "IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @TableSchema + '].[' + @TableName + ']') AND UPPER(name) = UPPER(@Name))");
                    buffer.AppendLine(
                        "    EXECUTE('DROP INDEX [' + @Name + '] ON [' + @TableSchema + '].[' + @TableName + ']')");
                    buffer.AppendLine();
                    buffer.AppendLine(@"PRINT 'Creating index [' + @Name + '] on table [' + @TableSchema + '].[' + @TableName + ']'");
                    buffer.AppendLine("GO");
                    buffer.AppendLine();
                    buffer.Append(@"CREATE");
                    if (index.IsUnique)
                    {
                        buffer.Append(" UNIQUE");
                    }

                    buffer.AppendFormat(" {0} INDEX [{1}]", index.Type, index.Name);
                    buffer.AppendLine();
                    buffer.AppendFormat("ON [{0}].[{1}]", index.Table.Schema, index.Table.Name);
                    buffer.AppendFormat("({0})", string.Join(", ", index.Columns.Where(c => !c.IsIncluded).Select(
                                                                 c =>
                    {
                        if (c.IsDescending)
                        {
                            return(c.Name + " DESC");
                        }

                        return(c.Name);
                    })));
                    if (index.Columns.Any(c => c.IsIncluded))
                    {
                        buffer.AppendLine();
                        buffer.AppendFormat("INCLUDE ({0})", string.Join(", ", index.Columns.Where(c => c.IsIncluded).Select(c => c.Name)));
                    }

                    if (index.HasFilter)
                    {
                        buffer.AppendLine();
                        buffer.AppendFormat("WHERE {0}", index.FilterDefinition);
                    }

                    obj.Code += buffer.ToString();
                    obj.AddCodeTemplate();
                }
            }
        }
예제 #2
0
        public override void AddCode(SqlConnection connection, SqlObject obj)
        {
            SysIndex index = null;
            using (var cmd = new SqlCommand(@"SELECT ind.index_id AS IndexId
,ind.name AS IndexName
,col.name AS ColumnName
,t.name AS TableName
,s.name AS SchemaName
,ic.index_column_id AS ColumnId
,ind.is_unique AS IsUnique
,ind.type_desc as Type
,ind.has_filter AS HasFilter
,ind.filter_definition AS FilterDefinition
,ic.is_descending_key AS IsDescending
,ic.is_included_column AS IsIncluded
FROM sys.indexes ind 
JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
JOIN sys.tables t ON ind.object_id = t.object_id 
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ind.name = @NAME
  AND ind.object_id = @ID
ORDER BY TableName
,IndexId
,ColumnId", connection))
            {
                cmd.Parameters.Add("@NAME", SqlDbType.NVarChar, 128);
                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters["@NAME"].Value = obj.Name;
                cmd.Parameters["@ID"].Value = obj.ObjectId;
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.HasRows) return;
                    while (reader.Read())
                    {
                        if (index == null)
                        {
                            var tbl = new SysTable
                            {
                                Name = reader[3].ToString(),
                                Schema = reader[4].ToString()
                            };

                            index = new SysIndex
                            {
                                Id = Convert.ToInt32(reader[0]),
                                Name = reader[1].ToString(),
                                Table = tbl,
                                Type = reader[7].ToString(),
                                IsUnique = Convert.ToBoolean(reader[6]),
                                HasFilter = Convert.ToBoolean(reader[8]),
                                FilterDefinition = Convert.ToString(reader[9])
                            };
                        }

                        index.Columns.Add(new SysIndexColumn
                        {
                            Id = Convert.ToInt32(reader[5]),
                            Name = Convert.ToString(reader[2]),
                            IsDescending = Convert.ToBoolean(reader[10]),
                            IsIncluded = Convert.ToBoolean(reader[11]),
                        });
                    }
                    var buffer = new StringBuilder();
                    buffer.AppendLine(@"DECLARE @Name nvarchar(128), @TableName nvarchar(128), @TableSchema nvarchar(128)");
                    buffer.AppendFormat(
                        @"SELECT @Name = N'{0}', @TableName=N'{1}', @TableSchema = N'{2}'",
                        index.Name,
                        index.Table.Name,
                        index.Table.Schema);
                    buffer.AppendLine();
                    buffer.AppendLine(
                        "IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[' + @TableSchema + '].[' + @TableName + ']') AND UPPER(name) = UPPER(@Name))");
                    buffer.AppendLine(
                        "    EXECUTE('DROP INDEX [' + @Name + '] ON [' + @TableSchema + '].[' + @TableName + ']')");
                    buffer.AppendLine();
                    buffer.AppendLine(@"PRINT 'Creating index [' + @Name + '] on table [' + @TableSchema + '].[' + @TableName + ']'");
                    buffer.AppendLine("GO");
                    buffer.AppendLine();
                    buffer.Append(@"CREATE");
                    if (index.IsUnique)
                    {
                        buffer.Append(" UNIQUE");
                    }

                    buffer.AppendFormat(" {0} INDEX [{1}]", index.Type, index.Name);
                    buffer.AppendLine();
                    buffer.AppendFormat("ON [{0}].[{1}]", index.Table.Schema, index.Table.Name);
                    buffer.AppendFormat("({0})", string.Join(", ", index.Columns.Where(c => !c.IsIncluded).Select(
                        c =>
                        {
                            if (c.IsDescending)
                            {
                                return c.Name + " DESC";
                            }

                            return c.Name;
                        })));
                    if (index.Columns.Any(c => c.IsIncluded))
                    {
                        buffer.AppendLine();
                        buffer.AppendFormat("INCLUDE ({0})", string.Join(", ", index.Columns.Where(c => c.IsIncluded).Select(c => c.Name)));
                    }

                    if (index.HasFilter)
                    {
                        buffer.AppendLine();
                        buffer.AppendFormat("WHERE {0}", index.FilterDefinition);
                    }

                    obj.Code += buffer.ToString();
                    obj.AddCodeTemplate();
                }
            }
        }