Raising links through T-SQL statements |  |
The following document describes how Enlighten handles links raised through T-SQL statements.
The following table lists objects that can be linked:
|
| Bits are combined in an entity of 64 bits divided into a "high" section and a "low" section. The link relation type is an OU bit to the bit (bitwise OR) of the elements described in the Excel file below:
|
Relational statements that raise links
Most links are generated during analysis depending on the statement being processed. Links between a host object and a referenced object are handled when using one of the following statements:
These are known as RELATIONAL STATEMENTS.
|
Script example for relational statements
| Create Proc dbo.P_12520 As Begin --This line must remain at line 10 of the tests procedure --CREATE TABLE T_for_DBCC1( C int ), T_for_DBCC2( C int ), etc... --CREATE TABLE [T_for_DBCC3.ter] ( C int ) --CREATE TABLE [T_for_DBCC4[qua] ( C int ) --CREATE TABLE [T_for_DBCC5]]cin] ( C int ) DBCC checktable( T_for_DBCC1 ) -- 15 DBCC checktable( "[dbo].[T_for_DBCC1]" ) -- 16 DBCC checktable( [T_for_DBCC1*bis] ) -- 17 DBCC checktable( '[T_for_DBCC1*bis]' ) -- 18 DBCC checktable( 'T_for_DBCC1*bis' ) -- 19 DBCC checktable( "T_for_DBCC1*bis" ) -- 20 DBCC checktable( 'dbo.T_for_DBCC2' ) -- 21 DBCC checktable( 'dbo.[T_for_DBCC2''bis]' ) -- 22 DBCC checktable( 'dbo.T_for_DBCC2''bis' ) -- 23 DBCC checktable( 'ACA_db1.dbo.T_for_DBCC3' ) -- 24 DBCC checktable( 'ACA_db1..T_for_DBCC4' ) -- 25 DBCC checktable( 'X1' ) -- 26 DBCC checktable( [X2] ) -- 27 DBCC checktable( "X3" ) -- 28 DBCC checktable( 'dbo.X4' ) -- 29 DBCC checktable( [dbo.X5] ) -- 30 DBCC checktable( "dbo.X6" ) -- 31 DBCC checktable( '[dbo].[X7]' ) -- 32 DBCC checktable( ["dbo"."X8"] ) -- 33 DBCC checktable( "[dbo].[X9]" ) -- 34 DBCC checktable( '[T_for_DBCC3.ter]' ) -- 35 DBCC checktable( "[T_for_DBCC3.ter]" ) -- 36 DBCC checktable( '[T_for_DBCC4[qua]' ) -- 37 DBCC checktable( [[T_for_DBCC4[qua]]] ) -- 38 DBCC checktable( '[T_for_DBCC5]]cin]' ) -- 39 DBCC checktable( [[T_for_DBCC5]]]]cin]]] ) -- 40 -- must force the display of a message for each line of code below DBCC checktable( unknownT1 ) DBCC checktable( 'dbo.unknownT2' ) DBCC checktable( 'ACA_db1..unknownT3' ) -- Tests that the access type INSERT is enriched with CREATE SELECT C=1 INTO T_for_SelectInto RETURN -- for safety due to recursive calls below! EXEC P_12520 EXEC dbo.P_12520 EXEC ACA_db1.dbo.P_12520 EXEC ACA_db1. .P_12520 EXEC P_params 1, 'toto' EXEC P_PaRaMs 1, 'toto' EXEC dbo.P_PaRaMs 1, 'toto' EXEC sp_who EXEC SP_WHO EXEC sP_WhO EXEC master..sp_who EXEC master..SP_WHO EXEC MASTER..sP_WhO select * from ::FN_helpcollations() select * from ::fn_helpcollations() EXECUTE sp_rename T_for_DBCC1 , 'X' EXECUTE sp_rename "[dbo].[T_for_DBCC1]" , 'X' EXECUTE sp_rename [T_for_DBCC1*bis] , 'X' EXECUTE sp_rename '[T_for_DBCC1*bis]' , 'X' EXECUTE sp_rename 'T_for_DBCC1*bis' , 'X' EXECUTE sp_rename "T_for_DBCC1*bis" , 'X' EXECUTE sp_rename 'dbo.T_for_DBCC2' , 'X' EXECUTE sp_rename 'dbo.[T_for_DBCC2''bis]' , 'X' EXECUTE sp_rename 'dbo.T_for_DBCC2''bis' , 'X' EXECUTE sp_rename 'ACA_db1.dbo.T_for_DBCC3' , 'X' EXECUTE sp_rename 'ACA_db1..T_for_DBCC4' , 'X' EXECUTE sp_rename 'X1' , 'X' EXECUTE sp_rename [X2] , 'X' EXECUTE sp_rename "X3" , 'X' EXECUTE sp_rename 'dbo.X4' , 'X' EXECUTE sp_rename [dbo.X5] , 'X' EXECUTE sp_rename "dbo.X6" , 'X' EXECUTE sp_rename '[dbo].[X7]' , 'X' EXECUTE sp_rename ["dbo"."X8"] , 'X' EXECUTE sp_rename "[dbo].[X9]" , 'X' EXECUTE sp_rename '[T_for_DBCC3.ter]' , 'X' EXECUTE sp_rename "[T_for_DBCC3.ter]" , 'X' EXECUTE sp_rename '[T_for_DBCC4[qua]' , 'X' EXECUTE sp_rename [[T_for_DBCC4[qua]]] , 'X' EXECUTE sp_rename '[T_for_DBCC5]]cin]' , 'X' EXECUTE sp_rename [[T_for_DBCC5]]]]cin]]] , 'X' /*.. ------------------------------------------------------------------------------------- -- Because these ODBC CALL escape clauses are changed to EXEC orders -- at compilation, they must be retained in the text to check that the analyzer will -- analyze them (this is necessary for the synchronization in SB/S, that sends -- the text in RAM where these calls have not yet been changed into EXECUTE -- orders on the fly. ------------------------------------------------------------------------------------- { CALL sp_who1 ('toto') } { CALL sp_who2;2('toto') } { CALL dbo.sp_raclure1 } { CALL dbo.sp_raclure2;2 } { CALL CASTKB..cleanup1 } { CALL CASTKB..cleanup2;2 } DECLARE @x int DECLARE @fnc_name sysname SELECT @x = 2 SELECT @fnc_name = 'ACA_for_DF.dbo.FN_IncOf1' { CALL @fnc_name(@x) } DECLARE @login varchar(30) DECLARE @spr_name sysname SELECT @login = 'sa' SELECT @spr_name = 'sp_who' { CALL @spr_name( 'sa' ) } { CALL @spr_name;1( 'sa' ) } { CALL @spr_name( @login ) } { CALL @spr_name;1( @login ) } ..*/ -- Non regression tests to check that the EXEC is still recognised EXEC toto1 EXEC toto2;2 INSERT T(C) EXECUTE dbo.titi1 INSERT T(C) EXECUTE dbo.titi2;2 -- create table T_for_index( C int not null ) CREATE UNIQUE CLUSTERED INDEX I_on_T_for_index1 ON T_for_index( C ) DROP INDEX T_for_index1.I_on_T_for_index1 DROP INDEX dbo.T_for_index1.I_on_T_for_index1 DROP INDEX #T_for_index2.I_on_#T_for_index2 DROP INDEX dbo.#T_for_index2.I_on_#T_for_index2 -- create table T_for_BulkInsert( C numeric identity not null ) SET IDENTITY_INSERT T_for_BulkInsert ON BULK INSERT T_for_BulkInsert FROM 'f:\my_data.tbl' SET IDENTITY_INSERT T_for_IdentityInsert OFF -- NB: Certains des ordres ci-dessous sont debiles/impossibles -- Ils ne sont la que pour verifier que l'objet accede est bien trouvee dans tous les cas. ALTER TABLE dbo.Ta ADD C int not NULL, X int not NULL ALTER TABLE Tb ADD C int NOT NULL ALTER TABLE Tc ADD ComputedCol AS ( C + C1 + C2 ) ALTER TABLE "dbo".[Td] ADD "C" AS ( 3*X.C + 2*T.X + 2*[C] + ( SELECT (2*T.C) FROM Te ) + 1 ) ALTER TABLE [dbo].[Tf] ADD [C] AS ( 3*X.C + 2*T.X + 2*[C] + ( SELECT (2*[T].[C]) FROM T ) + 1 ) ALTER TABLE ."dbo"."Tg" ADD C AS ( 3*X.C + 2*T.X + 2*[C] + ( SELECT (2*T.C) ) + 1 ) ALTER TABLE ."dbo".[Th] ADD "C" AS ( 3*X.C + 2*T.X + 2*[C] + ( SELECT (2*[T].[C]) ) + 1 ) ALTER TABLE Ti DROP COLUMN C, ComputedCol ALTER TABLE Tj ADD C AS ( 2*(-([C])) + ( cos("C") * sin(C) ) ) ALTER TABLE Tk ALTER COLUMN C int NULL ALTER TABLE Tl WITH NOCHECK ADD CONSTRAINT MyConstraint CHECK ((C+cos(C)) > 1-(C*"C")) ALTER TABLE Tm WITH NOCHECK ADD CONSTRAINT C CHECK ((C+cos(C)) > 1-(C*"C")) ALTER TABLE Tn ADD C INT IDENTITY CONSTRAINT MyConstraint PRIMARY KEY ALTER TABLE Tp ADD C INT IDENTITY CONSTRAINT C PRIMARY KEY ALTER TABLE Tq1 ADD C INT NULL CONSTRAINT MyConstraint REFERENCES Tq2(C) ALTER TABLE Tr1 ADD C INT NULL CONSTRAINT C REFERENCES Tr2(C) ALTER TABLE Ts1 ADD C INT NULL CONSTRAINT MyConstraint REFERENCES Ts2( [C] ) ALTER TABLE Tt1 ADD C INT NULL CONSTRAINT C REFERENCES Tt2( [C] ) ALTER TABLE Tu1 ADD C INT CONSTRAINT MyConstraint REFERENCES Tu2 ALTER TABLE Tv1 ADD C INT CONSTRAINT C REFERENCES Tv2 SET IDENTITY_INSERT Tw ON DROP STATISTICS Tx.Stat1, dbo.Ty.Stat2, .Tz.Stat4 DROP TABLE TTa DROP TABLE TTb, "TTc", [TTd] CREATE UNIQUE INDEX I ON TTe ( C ) DROP INDEX TTe.I DENY ALL ( C ) ON TTf TO dbo DENY ALL ON TTg( C ) TO dbo DUMP TABLE TTh GRANT SELECT ON TTi ( C, C1, C2, X ) TO [public] GRANT SELECT ON TTj TO dbo GRANT EXECUTE ON TTk TO dbo REVOKE SELECT ON TTl ( C, C1, C2, X ) TO [public] REVOKE SELECT ON TTm TO dbo REVOKE EXECUTE ON TTn TO dbo DROP PROCEDURE P_dropped DROP FUNCTION dbo.F_dropped DROP TRIGGER TR_dropped SELECT * FROM #TempTA1 SELECT * FROM ##TempTA2 EXEC #TempSP1 EXEC ##TempSP2 DECLARE @CalledProc varchar(30) SELECT @CalledProc = 'sp_who' EXEC @CalledProc -- Test of READTEXT, WRITETEXT, UPDATETEXT ------------------------------------------ DECLARE @ptrval varbinary(16) SELECT @ptrval = NULL READTEXT T_8451_for_RT_1.C @ptrval 1 2 READTEXT T_8451_for_RT_2.C @ptrval 1 2 HOLDLOCK READTEXT [T_8451_for_RT_1b].C @ptrval 1 2 READTEXT [T_8451_for_RT_2b].C @ptrval 1 2 HOLDLOCK READTEXT "T_8451_for_RT_1q".C @ptrval 1 2 READTEXT "T_8451_for_RT_2q".C @ptrval 1 2 HOLDLOCK WRITETEXT T_8451_for_WT.C @ptrval 'New Moon Books (NMB)' WRITETEXT [T_8451_for_WTb].C @ptrval 'New Moon Books (NMB)' WRITETEXT "T_8451_for_WTq".C @ptrval 'New Moon Books (NMB)' UPDATETEXT T_8451_for_UT_1.C @ptrval 88 1 'n' UPDATETEXT T_8451_for_UT_2.C @ptrval 88 1 T_8451_for_UT_3.C @ptrval UPDATETEXT [T_8451_for_UT_1b].C @ptrval 88 1 'b' UPDATETEXT [T_8451_for_UT_2b].C @ptrval 88 1 [T_8451_for_UT_3b].C @ptrval UPDATETEXT "T_8451_for_UT_1q".C @ptrval 88 1 'q' UPDATETEXT "T_8451_for_UT_2q".C @ptrval 88 1 "T_8451_for_UT_3q".C @ptrval -- Test of OPENXML ... WITH <table> -- NB: Sur TSQL_m80, une tentative d'utilisation d'un nom de vue dans la WITH-clause -- de l'instruction OPENXML(...) est accepte par le parseur MS, mais se solde par -- une "fatal exception" qui tue la connexion => toujours une TABLE ci dessous. ------------------------------------------------------------------------------------- DECLARE @idoc int SELECT @idoc = 0 SELECT * FROM OPENXML( @idoc, '/ROOT/Customer/Order/OrderDetail', 2 ) WITH T_8451_for_OPENXML SELECT * FROM OPENXML( @idoc, '/ROOT/Customer/Order/OrderDetail', 2 ) WITH [T_8451_for_OPENXMLb] SELECT * FROM OPENXML( @idoc, '/ROOT/Customer/Order/OrderDetail', 2 ) WITH "T_8451_for_OPENXMLq" ---------------------------------------------- CREATE TABLE T_for_ManyAccesses( C int ) READTEXT T_for_ManyAccesses.C @ptrval 1 2 WRITETEXT T_for_ManyAccesses.C @ptrval 'New Moon Books (NMB)' UPDATETEXT T_for_ManyAccesses.C @ptrval 88 1 T_for_ManyAccesses.C @ptrval SELECT * FROM OPENXML( @idoc, '/ROOT/Customer/Order/OrderDetail', 2 ) WITH T_for_ManyAccesses SELECT * FROM T_for_ManyAccesses INSERT T_for_ManyAccesses VALUES ( 1 ) DELETE FROM T_for_ManyAccesses TRUNCATE TABLE T_for_ManyAccesses UPDATE T_for_ManyAccesses SET C = 1 WHERE C = 2 DROP TABLE T_for_ManyAccesses ALTER TABLE T_for_ManyAccesses ADD C INT NULL CONSTRAINT C REFERENCES T_for_ManyAccesses( [C] ) declare @SqlError int RAISERROR(15007,-1,-1,'dbo') RAISERROR ('Raise error message text here', 16, 2) RAISERROR 21013 'Acc_DelFromDate: Protocol mismatch.' RAISERROR 50001 'SP CreateNewInvoice failed !!!' raiserror @SqlError '' -- Partie de CREATE STATISTICS valable sur MS 7.0 & MS 2K CREATE STATISTICS Stat1 ON X1( x ) CREATE STATISTICS Stat2 ON X2( "x" ) WITH FULLSCAN CREATE STATISTICS Stat3 ON X3( [x] ) WITH SAMPLE 5 PERCENT CREATE STATISTICS Stat4 ON X4( x ) WITH SAMPLE 5 PERCENT, NORECOMPUTE CREATE STATISTICS Stat5 ON X5( x ) WITH NORECOMPUTE, SAMPLE 5 PERCENT CREATE STATISTICS Stat6 ON dbo.X6( "x", x ) WITH FULLSCAN, NORECOMPUTE CREATE STATISTICS Stat7 ON ACA_db1..X7( x, [x], "x", x ) WITH NORECOMPUTE CREATE STATISTICS Stat8 ON ACA_db1.dbo.X8( x ) WITH FULLSCAN --.. CREATE STATISTICS Stat1 ON X9( x ) WITH SAMPLE 5 PERCENT, NORECOMPUTE, FULLSCAN --.. CREATE STATISTICS Stat1 ON X9( x ) WITH FULLSCAN, NORECOMPUTE, SAMPLE 5 PERCENT, FULLSCAN --.. CREATE STATISTICS Stat1 ON dbo.X9( x ) WITH FULLSCAN, toto, NORECOMPUTE /* option 'toto' inconnue */ --Test pour la CLIA --CREATE TABLE T_for_Statistics( C1 int, C2 int, C3 int ) CREATE STATISTICS StatCLIA ON T_for_Statistics( C1, C2, C3 ) WITH SAMPLE 5 ROWS CREATE STATISTICS StatCLIA ON dbo.T_for_Statistics( C1, C2, C3 ) WITH SAMPLE 5 ROWS CREATE STATISTICS StatCLIA ON ACA_db1..T_for_Statistics( C1, C2, C3 ) WITH SAMPLE 5 ROWS -- Partie de CREATE STATISTICS valable sur MS 2K seulement a cause de "ROWS" CREATE STATISTICS Stat1 ON X1( x ) WITH SAMPLE 5 ROWS CREATE STATISTICS Stat2 ON X2( x ) WITH SAMPLE 5 ROWS, NORECOMPUTE CREATE STATISTICS Stat3 ON X3( x ) WITH NORECOMPUTE, SAMPLE 5 ROWS --.. CREATE STATISTICS Stat1 ON X4( x ) WITH FULLSCAN, SAMPLE 5 ROWS, NORECOMPUTE --.. CREATE STATISTICS Stat1 ON X5( x ) WITH NORECOMPUTE, SAMPLE 5 ROWS, FULLSCAN CREATE STATISTICS StatA ON #STAT_TABLE( x ) WITH SAMPLE 5 ROWS CREATE STATISTICS StatB ON UNEX_STAT_TABLE( x ) WITH SAMPLE 5 ROWS -- Ordres reconnus pour toute versions >= 6.5 --------------------------------------------- declare @table_name char(30) BACKUP TABLE MaTable BACKUP TABLE MaTable TO DumpDevice BACKUP TABLE @table_name BACKUP TABLE @table_name TO DumpDevice RESTORE TABLE MaTable RESTORE TABLE MaTable FROM DumpDevice RESTORE TABLE @table_name RESTORE TABLE @table_name FROM DumpDevice LOAD TABLE dbo.T RESTORE TABLE dbo.T FROM DumpDevice LOAD TABLE T FROM DumpDevice RESTORE TABLE T LOAD TABLE ACA_bug_4090.dbo.T RESTORE TABLE ACA_bug_4090.dbo.T FROM DumpDevice LOAD TABLE ACA_bug_4090..T FROM DumpDevice RESTORE TABLE ACA_bug_4090..T -- Ordres reconnus pour toute versions >= 7.0 --------------------------------------------- DUMP log tempdb WITH no_log DUMP tran tempdb WITH no_log BACKUP log tempdb WITH no_log BACKUP tran tempdb WITH no_log LOAD LOG MyNwind LOAD TRAN MyNwind RESTORE LOG MyNwind RESTORE TRAN MyNwind LOAD LOG MyNwind FROM DumpDevice LOAD TRAN MyNwind FROM DumpDevice LOAD HEADERONLY FROM DumpDevice RESTORE LOG MyNwind FROM DumpDevice RESTORE TRAN MyNwind FROM DumpDevice RESTORE HEADERONLY FROM DumpDevice BACKUP DATABASE tempdb BACKUP DATABASE tempdb TO DumpDevice RESTORE DATABASE tempdb RESTORE DATABASE tempdb FROM DumpDevice end |
