So Ändern Sie Alle Sql-Spalten von Einem Datentyp in einen Anderen

Ich habe eine Datenbank (Sql Server 2005) wo gibt es Dutzende von tables, von denen jeder eine Anzahl von columns (durchschnittlich 10-20) mit Datentyp festgelegt nvarchar(max). Das ist absolut das töten von Leistung (einige dieser Spalten sind für joins und einige Tische haben 100K+ Zeilen). Ich möchte alle diese Spalten werden varchar(250). Was wäre der beste Weg, dies zu automatisieren? (Ich könnte Management Studio oder ich erstellen Sie ein Dienstprogramm ausführen, das durch eine ASP.net website, die Zugriff auf die db, je nachdem was einfacher ist).

InformationsquelleAutor Yaakov Ellis | 2009-08-25
  1. 11

    Hier ist ein funktionierendes script, das verwendet INFORMATION_SCHEMA.COLUMNS finden Sie alle *varchar(max) Spalten und wandelt Sie in varchar(255):

    declare @schema nvarchar(255)
declare @table nvarchar(255)
declare @col nvarchar(255)
declare @dtype nvarchar(255)
declare @sql nvarchar(max)

declare maxcols cursor for
select
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE
from
INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t on
    c.TABLE_CATALOG = t.TABLE_CATALOG
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = t.TABLE_NAME
    and t.TABLE_TYPE = 'BASE TABLE'
where
    c.DATA_TYPE like '%varchar'
    and c.CHARACTER_MAXIMUM_LENGTH = -1

open maxcols

fetch next from maxcols into @schema, @table, @col, @dtype

while @@FETCH_STATUS = 0
begin
    set @sql = 'alter table [' + @schema + '].[' + @table + 
        '] alter column [' + @col + '] ' + @dtype + '(255)'
    exec sp_executesql @sql

    fetch next from maxcols into @schema, @table, @col, @dtype
end

close maxcols
deallocate maxcols

    Dies ist nur über das verwenden von Cursorn, die ich jemals dulden, aber es ist gut. Im wesentlichen, es findet alle *varchar(max) baut, die alter - Anweisung aus und führt Sie mit sp_executesql.

    Genießen!

    • Das funktioniert nicht perfekt, da bringt es auch in Spalten von Ansichten
    • Behoben!
    • Nun, wie man Nebenbedingungen und setzen Sie diese wieder ein, während dies zu tun, da wir Sie nicht ändern cols mit einer Einschränkung im Ort?
    • Wie etwa eine Prüfung, nur für Spalten mit max Länge < 255, so dass keine Daten möglicherweise abgeschnitten?
    InformationsquelleAutor Eric
  2. 3

    Können Sie leicht finden, Sie, mit:

    select 'alter table ' + quotename(o.name) + ' alter column ' + quotename(c.name) + ' varchar(250); '
from sys.columns c
  join
  sys.objects o
  on o.object_id = c.object_id
where o.type = 'U'
and c.user_type_id = 231
and c.max_length = -1

    So, jetzt nehmen Sie einfach die Ergebnisse Ihrer Abfrage, und führen Sie es.

    Rob

    • Vielleicht auch erwähnenswert..., wenn Sie declare @qry nvarchar(max), und wählen Sie @qry = (.... for xml path(")); exec sp_executesql @qry; - Sie können tun es alle in einem Schritt. Die .... die aktuelle Abfrage, die ich habe. Aber dann kann man nicht 'sanity check' das so gut.
    • Brauchen Sie, um die null-Zulässigkeit der Spalte in Betracht? Wird diese sich verändern, Spalten null-Werte zulassen, weil Sie nicht angeben oder so?
    InformationsquelleAutor Rob Farley
  3. 0

    Ich denke, der beste Weg, um diese Aufgabe zu erfüllen ist, um ein Skript zu generieren, die für die aktuelle Datenbank, und ersetzen nvarchar(max) mit varchar(250) auf die text-Datei, und erstellen Sie die neue Datenbank.
    danach verwenden Sie die Import - /export-Dienstprogramme zur übertragung der Daten in die neue Datenbank.

    • Das funktioniert nicht - SSIS eine Fehlermeldung zurück, die besagt, dass die Spalte Metadaten entsprechen nicht zwischen den Quell-und Ziel-Tabellen
    InformationsquelleAutor Wael Dalloul
Schreibe einen Kommentar