ENL - Raising Links through T-SQL Statements
Raising links through T-SQL statements |  |
The following document describes how Enlighten handles links raised through T-SQL statements.
Link types
The following table lists objects that can be linked:
|
Link type identification bits
| 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
|
