SQL Server – тип данных каскадного SQL-кода столбца

Это такая же старая проблема, как и холмы, и почти все столкнулись с ней. Итак, я ищу наиболее распространенное и наиболее надежное решение.

Если у меня есть столбец первичного ключа в таблице, другие таблицы имеют внешние ключи, связанные с этим столбцом, и я хочу изменить тип данных столбца, что является лучшим инструментом для достижения этого без необходимости писать специальные сценарии базы данных вручную , и без необходимости удаления данных? Есть ли для этого инструмент?

Итак, скажем, например, у меня есть две таблицы

Продажа

SaleKey (int)

Итого (десятичный)

SaleLine

SaleLineKey (int)

Продажа (int) <- Иностранный ключ обратно к продаже

Количество (int)

Скорость (десятичная)

Допустим, я хочу оптовой передел колонки SaleKey on Sale на уникальный идентификатор. Это означает, что мне нужно написать сценарий базы данных, чтобы добавить новый уникальный столбец идентификатора в продаже, добавить аналогичный столбец в SaleLine, обновить данные в SaleLine, чтобы отразить продажу, сбросить внешние и первичные ключи, а затем добавить новые иностранные и первичные ключи. Это все возможно, но требует много времени.

Есть ли там приложение, которое сделает это для меня? Или, хранимый процесс, который кто-то уже написал? SQL Server имеет каскадное удаление данных, как насчет типа данных каскадного изменения?

    Это решение с C #. Вам нужно будет заполнить пробелы в терминах ExecuteScript и ExecuteScalar, но логика будет работать до тех пор, пока вы используете SQL Server. Это будет преобразовывать только в uniqueidentifier, но изменить его для другого типа данных было бы непросто. Здесь есть несколько предположений. Например, этот код предполагает, что столбец, который вы пытаетесь изменить, имеет максимум один первичный ключ. Возможно, это хороший проект, чтобы переместить это в репозиторий с открытым исходным кодом в какой-то момент. Кроме того, это можно легко преобразовать в T / SQL. Я просто лучше с C #, чем T / SQL.

    Предупреждение: Это деструктивный код. Он без разбора удалит индексы и ключи и т. Д. Таким образом, при выполнении этого необходимо позаботиться, чтобы убедиться, что результирующие столбцы соответствуют вашей предполагаемой схеме данных. Это всего лишь инструмент для достижения поставленной задачи – не пуленепробиваемого решения. Тем не менее, это было тщательно проверено в наших базах данных.

    public void ChangeColumnDataTypeToUniqueIdentifier(string schemaName, string tableName, string columnName, bool allowsNull, ConnectionInfo connectionInfo) { string script = null; var tempColumnName = $"{columnName}Temp"; var columnDataTypeString = DatabaseAdapter.SqlStatementBuilder.GetSqlDataType(ColumnDataType.UniqueIdentifier, 0, 0, 0); //Add the temp column //TODO: We should try to figure out if this needs not null, but we can rely on the schema upgrade to fix this later... ExecuteScript(connectionInfo, "alter table " + $" {schemaName}.{tableName} " + "add " + $" {tempColumnName} {columnDataTypeString}"); var fullTableName = $"[{schemaName}].[{tableName}]"; //Update the temp column to new values //TODO: this contains UniqueIdentifier specific code ExecuteScript(connectionInfo, "update " + $" {fullTableName} " + "set " + $" {tempColumnName} = NEWID()"); ExecuteScript(connectionInfo, "alter table " + $" {schemaName}.{tableName} " + "alter column " + $" {tempColumnName} {columnDataTypeString} {(!allowsNull ? string.Empty : "not")} null"); //Get the schema id of the target table var targetSchemaObjectId = (int)DatabaseAdapter.ExecuteScalar("select schema_id from sys.schemas where name = @Param1", new Collection<DataParameter> { new DataParameter("Param1", schemaName) }, connectionInfo, null); //Get the object id of the table we are modifying var targetTableObjectId = (int)DatabaseAdapter.ExecuteScalar("select object_Id from sys.tables where name = @Param1 and schema_id = @Param2", new Collection<DataParameter> { new DataParameter("Param1", tableName), new DataParameter("Param2", targetSchemaObjectId) }, connectionInfo, null); //Get foreign keys using (var foreignKeyData = DatabaseAdapter.ExecuteReader("select * from Sys.foreign_keys where referenced_object_id = @Param1", new Collection<DataParameter> { new DataParameter("Param1", targetTableObjectId) }, connectionInfo, null)) { //Iterate through foreign keys while (foreignKeyData.DataReader.Read()) { //Get thei object id of the table that references this var tableThatReferencesThisObjectId = (int)foreignKeyData.DataReader["parent_object_Id"]; var foreignKeyObjectId = (int)foreignKeyData.DataReader["object_Id"]; var foreignKeyName = (string)foreignKeyData.DataReader["name"]; //Get the tables data using (var tableThatReferencesThisData = DatabaseAdapter.ExecuteReader("select * from Sys.tables where object_id = @Param1", new Collection<DataParameter> { new DataParameter("Param1", tableThatReferencesThisObjectId) }, connectionInfo, null)) { //Read the record tableThatReferencesThisData.DataReader.Read(); //Get information about the table references this var tableThatReferencesThisName = (string)tableThatReferencesThisData.DataReader["name"]; var tableThatReferencesShemaObjectId = (int)tableThatReferencesThisData.DataReader["schema_id"]; var tableThatReferencesShemaName = (string)DatabaseAdapter.ExecuteScalar("select * from sys.schemas where schema_id = @Param1", new Collection<DataParameter> { new DataParameter("Param1", tableThatReferencesShemaObjectId) }, connectionInfo, null); //Get the name of the column that references the original column var foreignKeyColumnName = (string)DatabaseAdapter.ExecuteScalar ( "select " + " COL_NAME(fks.parent_object_id, fkcs.parent_column_id) " + "from " + " sys.foreign_keys fks " + "inner join " + " Sys.foreign_key_columns fkcs " + "on " + " fkcs.constraint_object_id = fks.object_id " + "where " + $" fks.object_id = {foreignKeyObjectId}", new Collection<DataParameter> { new DataParameter("Param1", tableThatReferencesShemaObjectId) }, connectionInfo, null); //The new target temp column name var tempForeignKeyColumnName = foreignKeyColumnName + "Temp"; //Concatenate the name of the table that references the original table var tableThatReferencesFullName = $"[{tableThatReferencesShemaName}].[{tableThatReferencesThisName}]"; //Add the temp column //TODO: We should try to figure out if this needs not null, but we can rely on the schema upgrade to fix this later... ExecuteScript(connectionInfo, "alter table " + $" {tableThatReferencesFullName} " + "add " + $" {tempForeignKeyColumnName} uniqueidentifier"); //Update the data in the temp column script = "update " + $" {tableThatReferencesFullName} " + "set " + $"{tempForeignKeyColumnName} = " + " ( " + " select " + $" {tempColumnName} " + " from " + $" {fullTableName} referencedtable " + " where " + $" {tableThatReferencesFullName}.[{foreignKeyColumnName}] = referencedtable.{columnName} " + " )"; ExecuteScript(connectionInfo, script); //Drop the original foreign key script = "alter table " + $" {tableThatReferencesFullName} " + "drop " + $" {foreignKeyName} "; ExecuteScript(connectionInfo, script); DropIndexesForTable(tableThatReferencesShemaName, tableThatReferencesThisName, foreignKeyColumnName, connectionInfo); //Drop the old column script = "alter table " + $" {tableThatReferencesFullName} " + "drop column " + $" [{foreignKeyColumnName}] "; ExecuteScript(connectionInfo, script); //Rename the new temp column to the old one ExecuteScript(connectionInfo, $"EXEC sp_rename '{tableThatReferencesFullName}.{tempForeignKeyColumnName}', '{foreignKeyColumnName}', 'COLUMN'"); } } } var pkName = (string)DatabaseAdapter.ExecuteScalar($"select name from sys.key_constraints where parent_object_id = @Param1 and type = 'PK'", new Collection<DataParameter> { new DataParameter("Param1", targetTableObjectId) }, connectionInfo, null); if (!string.IsNullOrEmpty(pkName)) { //Drop the old primary key script = "alter table " + $" {fullTableName} " + "drop " + $" {pkName} "; ExecuteScript(connectionInfo, script); } var defaultConstraintName = (string)DatabaseAdapter.ExecuteScalar( "select " + " dc.name " + "FROM " + " SYS.DEFAULT_CONSTRAINTS dc " + "inner join " + " sys.all_columns ac " + "on " + " ac.object_id = @ObjectId and " + " dc.parent_column_id = ac.column_id " + "where " + " parent_object_id = @ObjectId and " + " ac.name = @ColumnName", new Collection<DataParameter> { new DataParameter("ColumnName", columnName), new DataParameter("ObjectId", targetTableObjectId) }, connectionInfo, null); if (!string.IsNullOrEmpty(defaultConstraintName)) { //Drop the old primary key script = "alter table " + $" {fullTableName} " + "drop constraint" + $" {defaultConstraintName} "; ExecuteScript(connectionInfo, script); } DropIndexesForTable(schemaName, tableName, columnName, connectionInfo); //Drop the old column script = "alter table " + $" {fullTableName} " + "drop column " + $" {columnName}"; ExecuteScript(connectionInfo, script); //Rename the new column to the old one ExecuteScript(connectionInfo, $"EXEC sp_rename '{fullTableName}.{tempColumnName}', '{columnName}', 'COLUMN'"); } private void DropIndexesForTable(string schemaName, string tableName, string columnName, ConnectionInfo connectionInfo) { //find indexes dependent on this column string script = "SELECT " + " SchemaName = s.name, " + " TableName = t.name, " + " IndexName = ind.name " + "FROM " + " sys.indexes ind " + "INNER JOIN " + " sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id " + "INNER JOIN " + " sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id " + "INNER JOIN " + " sys.tables t ON ind.object_id = t.object_id " + "INNER JOIN " + " sys.schemas s on t.schema_id = s.schema_id " + "WHERE " + " ind.is_primary_key = 0 and" + " s.name = @SchemaName and " + " t.name = @TableName and " + " col.name = @ColumnName"; using (var obstructingIndexData = DatabaseAdapter.ExecuteReader(script, new Collection<DataParameter> { new DataParameter("SchemaName", schemaName), new DataParameter("TableName", tableName), new DataParameter("ColumnName", columnName) }, connectionInfo, null)) { while (obstructingIndexData.DataReader.Read()) { var indexSchema = obstructingIndexData.DataReader["SchemaName"]; var indexTable = obstructingIndexData.DataReader["TableName"]; var IndexName = obstructingIndexData.DataReader["IndexName"]; ExecuteScript(connectionInfo, $"drop index [{indexSchema}].[{indexTable}].[{IndexName}]"); } } } 
    Interesting Posts

    Нужно вычислять по округленному времени или дате в sql-сервере

    Лучший способ переноса xml на SQL Server?

    Как реализовать механизм статистики * в режиме реального времени из данных SQL-сервера для отображения панели мониторинга?

    возвращать только последние результаты выбора из хранимой процедуры

    Запрос Sql для выбора данных с предыдущего дня в 16:15:01 до сегодняшнего дня в 16:15:00

    Сохраненная процедура возвращает значения с контекстом данных linq

    Pentaho против Microsoft BI Stack

    счетчик (индексированный столбец) быстрее, чем count (*)?

    SQL-запрос, преобразованный в LINQ-DISTINCT.

    дизайн базы данных для онлайн-системы заказов

    Получить значение даты и времени из таблицы SQL Server с помощью C # Sql Reader

    SQL Indexing – вычисляемый столбец против поля, используемого вычислительной колонкой

    Тип данных SQL-Server

    Как отобразить фактический результат данных sql-сервера на странице HTML с помощью веб-службы?

    Поиск ковариации с использованием SQL

    Давайте будем гением компьютера.