Plötzlich Alias-Spaltennamen in View

[Andreas Mahr]

Hallo zusammen,

ich habe einen SQL-Server 2005 mit einer Reihe Tabellen und dazugehörigen Views. Jetzt habe ich an einigen Tabellen eine Spalte (an 2. Position) hinuzgefügt. Seit dem haben die entsprechenden Views, ohne mein Zutun, auch eine Spalte mehr und alle Spalten ab der 2. haben als Alias den Namen der Spalte, die vorher an dieser Stelle war.

Beispiel:

Alte Tabelle: ID, Text, Sortierung
Neue Tabelle: ID, Sprache, Text, Sortierung


View vorher: ID, Text, Sortierung
View nacher: ID, Sprache As Text, Text As Sortierung, Sortierung


Hat jemand eine Ahnung woher das kommt?

[Bernhard Geyer]

Wie schaut die View-Definition aus?

[Andreas Mahr]

Wenn ich die View im Editor öffne sieht das so aus

[highlight=sql]
Select ID, Sprache As Text, Text As Sortierung, Sortierung
From MeineTabelle
[/highlight]

EDIT:

Wenn ich über "Bearbeiten" gehe sieht sie allerdings so aus:

[highlight=sql]
Select MeineTabelle.*
From MeineTabelle
[/highlight]

[O. Helper]

1. Hast Du Dir schon mal angesehen, was das SSMS macht, wenn Du ein Feld mitdrin einfügst? Er legt eine neue Tabelle an, kopiert die Daten und droppt die alten.
2. Das ist "nur" eine Schwäche (oder Bug .. oder Feature) vom SSMS. Wenn Du das untenstehende Script ausführst siehst Du, es ist bis dahin soweit i.O.; nur sollte man nicht unbedingt im SSMS auch noch versuchen,

[highlight=SQL]USE [AdventureWorks]
GO
CREATE TABLE TestTable(id int, Datum datetime);
GO
CREATE VIEW vwTestTable
AS
SELECT *
FROM TestTable
GO
DROP TABLE TestTable
GO
CREATE TABLE TestTable(id int, Sprache varchar(4), Datum datetime);
GO

SELECT * FROM vwTestTable
GO
-- Create Statement
SELECT text
FROM sys.syscomments
WHERE text like '%vwTestTable%'
-- Columns
SELECT col.name
FROM sys.objects as obj
inner join
sys.columns as col on obj.object_id = col.object_id
WHERE obj.name = 'vwTestTable'

GO
DROP VIEW vwTestTable
DROP TABLE TestTable
[/highlight]
Ergbnis:
[highlight=code]id Datum
----------- -----

text
----------------------------------------
CREATE VIEW vwTestTable
AS
SELECT *
FROM TestTable

name
-----------
id
Datum[/highlight]

[Andreas Mahr]

hmm...ich müsste mir also jetzt ein Skript basteln, was grad über alle Views drüber flitzt, sie "öffnet", bzw ihren Text extrahiert und ausführt.
Dann sollte alles wieder passen.
Oder?

[O. Helper]

Njein. Am besten nicht Spalten in Tabellen mitten drin einfügen, aber das willst Du mit Sicherheit nicht von mir hören.

Du kannst von solchen Änderungen betroffenen Views aktualisieren lassen mit:

EXEC sp_refreshview 'vwTestTable'

Dann nimmt der SQL Server das neue Feld in die Definition für View mit auf und alles ist i.O., Du kannst es dann auch ohne Nebeneffekte im SSMS bearbeiten.

[Andreas Mahr]

ah, ok, mein Ansatz sah jetzt so aus

[highlight=sql]
declare @STCursor cursor
declare @befehl varchar(8000)

set @STCursor = CURSOR LOCAL FOR SELECT text FROM syscomments where id in (SELECT id FROM sysobjects where xtype='V')
OPEN @STCursor

FETCH NEXT FROM @STCursor INTO @befehl

WHILE @@FETCH_STATUS = 0
BEGIN
set @befehl = REPLACE(@befehl,'CREATE VIEW','ALTER VIEW')
exec(@befehl)
FETCH NEXT FROM @STCursor INTO @befehl
END
CLOSE @STCursor
DEALLOCATE @STCursor
[/highlight]

[O. Helper]

Da Du nur eigentlich nur die betroffenen (abhängigen) Views brauchst, kannst Du die selektieren und dann per Cursor / dyn. SQL die aktualisieren lassen; Select kann so aussehen:
[highlight=SQL]SELECT DISTINCT SCH.name AS SchemaName, OBJ.name AS ViewName
FROM sys.sql_dependencies AS DEP
INNER JOIN
sys.objects AS OBJ
ON DEP.object_id = OBJ.object_id
INNER JOIN
sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN
sys.objects AS REF
ON DEP.referenced_major_id = REF.object_id
WHERE REF.name = 'TestTable'
AND REF.type = 'U '
AND OBJ.type = 'V '[/highlight]

[Andreas Mahr]

ich hab das jetzt so gelösst:

[highlight=sql]
declare @STCursor cursor
declare @view varchar(500)
declare @befehl varchar(8000)

set @STCursor = CURSOR LOCAL FOR SELECT id FROM sysobjects where xtype='V'
OPEN @STCursor

FETCH NEXT FROM @STCursor INTO @view

WHILE @@FETCH_STATUS = 0
BEGIN
set @befehl = EXEC sp_refreshview '@view'
exec(@befehl)
FETCH NEXT FROM @STCursor INTO @view
END
CLOSE @STCursor
DEALLOCATE @STCursor
[/highlight]

Jetzt fällt das Skript aber immer wieder über alte Views, die nicht mehr funktionieren, weil die zugehörigen Tabellen gelöscht wurden. Da hat scheinbar nie jemand aufgeräumt.
Ich hab versucht sowas wie ON ERROR ins Skript zu machen, wo dann grad diese Views löscht, aber das will mir nicht gelingen.

[Andreas Mahr]

Da Du nur eigentlich nur die betroffenen (abhängigen) Views brauchst, kannst Du die selektieren und dann per Cursor / dyn. SQL die aktualisieren lassen; Select kann so aussehen:
[highlight=SQL]SELECT DISTINCT SCH.name AS SchemaName, OBJ.name AS ViewName
FROM sys.sql_dependencies AS DEP
INNER JOIN
sys.objects AS OBJ
ON DEP.object_id = OBJ.object_id
INNER JOIN
sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN
sys.objects AS REF
ON DEP.referenced_major_id = REF.object_id
WHERE REF.name = 'TestTable'
AND REF.type = 'U '
AND OBJ.type = 'V '[/highlight]

ja, schon, aber ich bekomme immer nur ein riesen Skript, das dann die Tabellen ändert. Und ich bin zu faul das dann durchzugehen und die Tabellennamen heraus zu schreiben

[Andreas Mahr]

ok, hab etwas das funktioniert:

[highlight=sql]
declare @STCursor cursor
declare @view varchar(500)
declare @befehl varchar(8000)

set @STCursor = CURSOR LOCAL FOR SELECT name FROM sysobjects where xtype='V'
OPEN @STCursor

FETCH NEXT FROM @STCursor INTO @view

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
exec sp_refreshview @view
END TRY
BEGIN CATCH
Print @view + ' wird gelöscht'
set @befehl = 'DROP VIEW ' + @view
exec(@befehl)
END CATCH
FETCH NEXT FROM @STCursor INTO @view
END
CLOSE @STCursor
DEALLOCATE @STCursor
[/highlight]