Page tree
Skip to end of metadata
Go to start of metadata

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:
  • Each ROW describes "outgoing relations" where Object 2 is the caller

  • Each COLUMN describes "incoming relations" where Object 1 is calling Object 2

  • X denotes objects where links can be created

Object 1 >

Object 2

^

VIEW

FUNCTION

MSGNo

TRIGGER

PROCEDURE

TABLE

NONTSQL

DYNAMIC

UNKNOWN

Foreign Key (FK)

Primary Key (PK)

TABLE

-

-

-

X

-

-

-

-

-

X

X

PROCEDURE

X

X

X

-

X

X

-

X

-

-

-

VIEW

X

X

-

X

-

X

-

-

-

-

-

FUNCTION

X

X

X

-

X

X

-

X

-

-

-

TRIGGER

X

X

X

-

X

X

-

X

-

-

-

DYNAMIC

-

-

-

-

-

- -

-

-

-

-

UNKNOWN

X

X

X

X

X

X

-

X

X

-

-

NONTSQL

X

X

-

-

X

X

X

-

-

-

-

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:

Notes

  • You may receive a warning dialog box when trying to access this .XLS file. This warning dialog will ask you whether you want to Open or Save the file - please select the option that is most convenient for you.

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:
  • EXECUTE, SELECT, UPDATE, INSERT, BULK INSERT, TRUNCATE TABLE, DROP TABLE, DELETE, OPENXML, READTEXT, WRITETEXT, UPDATETEXT.
  • Any type of link to an index
  • Links to tables/views via CREATE/UPDATE/DROP STATISTICS:

    CREATE STATISTICS (link type = rMENTION)

    DELETE STATISTICS (link type = rMENTION)

    UPDATE STATISTICS (link type = rMENTION)
  • Links to procedures via ODBC escape sequences (also for user-defined functions and "::fn_", i.e.: {CALL sp_who})
  • Links to tables/views taken into account via the orders listed below:

    CREATE [PROXY] TABLE (link type = rDDL + rCREATE)

    ALTER TABLE (link type to the table = rDDL + rALTER)

    ALTER TABLE ... ENABLE/DISABLE TRIGGER ALL/... (link type to the trigger(s) = rMENTION)

    DROP TABLE/VIEW/PROC/TRIGGER/FUNCTION (link type = rDDL + rDROP)

    LOCK TABLE (link type = rLOCK)

    LOAD/RESTORE TABLE (link type = rACCESS + rWRITE (table is written during loading from a dump device => 
    rWRITE))

    DUMP/BACKUP TABLE (link type = rACCESS + rREAD (table is read during dumping to a dump device => rREAD))

    CREATE INDEX (link type to a table = rMENTION - no link to the index)

    DROP INDEX (link type to a table = rMENTION - no link to the 'index)

    DBCC (link type = rMENTION) - The DBCC commands handled by the analyzer are: BUFFER, CHECKCONSTRAINTS, CHECKIDENT, CHECKTABLE, CLEANTABLE, DBREINDEX, DES, IND, INDEXDEFRAG, PINTABLE, PROCBUF, SHOW_STATISTICS, SHOWCONTIG, TEXTALLOC, UPDATEUSAGE, CHECKTABLE, FIX_TEXT, INDEXALLOC, OBJECT_ATTS, REBUILD_TEXT, REINDEX, TABLEALLOC, TUNE GRANT/REVOKE/DENY (link type = rMENTION)

    REORG (link type = rMENTION)

    SET IDENTITY_INSERT (link type = rMENTION)

    REFERENCES in a PK/FK link (link type = rMENTION)

    DROP STATISTICS (link type = rMENTION)

    BULK INSERT (link type = rUSE + rINSERT)
  • Links to procedures/tables/triggers/views/functions via the system procedures listed below:

    sp_help sp_bindefault sp_unbindefault sp_bindrule sp_unbindrule sp_primarykey sp_foreignkey sp_commonkey 
    sp_fulltext_column sp_fulltext_table sp_rename

These are known as RELATIONAL STATEMENTS.

Notes

  • The link type SELECT...INTO... has been enriched with rDDL + rCREATE as well as rUSE + rSELECT

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

CAST Website

  • No labels