גרי רשף

USE tempdb;

Go

Create Login DWL With Password='1234';

Go

Create User DWU for LOGIN DWL;

Go

Exec SP_AddroleMember 'DB_DataWriter',DWU;

Go

Create Table Tbl1(I Int, S Varchar(50));

Go

Insert Into Tbl1 Values(1,'A');

Insert Into Tbl1 Values(2,'B');

Insert Into Tbl1 Values(3,'C');

Go

Create Table Tbl2(S Varchar(50));

Go

Execute As User='DWU';

Go

Select * From Tbl1;

Go

Select * From sys.objects;

Go

Select * From sys.columns Where object_Name(Object_id)='Tbl1';

Go

Insert Into TBL2 Select '1';

Go

Insert Into TBL2 Select Cast(I As Varchar)+','+S From Tbl1;

Go

Insert Into TBL2 Select '1' Where Exists (Select * From Tbl1);

Go

Update TBL1

Set I=10*I;

Go

Delete From Tbl1 Output deleted.*;

Go

Delete From Tbl1;

Go

Revert;

Go

Drop User DWU;

Drop Login DWL;

Drop Table Tbl1;

Drop Table Tbl2;

Go

Use tempdb;

Go

Create Login MyLogin With Password='1234',Check_Policy=Off;

Go

Create User MyUser For Login MyLogin;

Go

Create Proc MyProc As Select GetDate() Taarih;

Go

Select * From sys.objects;

Go

Exec SP_HelpText 'MyProc';

Go

Execute As User='MyUser';

Go

Select * From sys.objects;

Go

Exec SP_HelpText 'MyProc';

Go

Revert;

Go

Grant View Definition On MyProc To MyUser;

Go

Execute As User='MyUser';

Go

Exec SP_HelpText 'MyProc';

Go

Revert;

Go

Grant View Definition TO MyUser;

Go

Execute As User='MyUser';

Go

Select * From sys.objects;

Go

Revert;

Go

Revoke View Definition To MyUser;

Revoke View Definition On MyProc To MyUser;

Go

Use tempdb;

Go

If Object_Id('Tbl1_tmp') Is Not Null Drop Table Tbl1_tmp;

If Object_Id('Tbl2_tmp') Is Not Null Drop Table Tbl2_tmp;

If Object_Id('Tbl1') Is Not Null Drop Table Tbl1;

If Object_Id('Tbl2') Is Not Null Drop Table Tbl2;

If Object_Id('Tavlaot') Is Not Null Drop Table Tavlaot;

Go

Create Table Tbl1_tmp(ID Int Identity Primary Key,

                    Txt Varchar(Max));

Create Table Tbl1(Taarih DateTime Not Null,

                ID Int Not Null,

                Txt Varchar(Max),

                Constraint PK_Tbl1 Primary Key Clustered(Taarih,ID));

Create Table Tbl2_tmp(ID Int Identity Primary Key,

                    Txt Varchar(Max),

                    Mispar Int);

Create Table Tbl2(Taarih DateTime Not Null,

                ID Int Not Null,

                Txt Varchar(Max),

                Mispar Int,

                Constraint PK_Tbl2 Primary Key Clustered(Taarih,ID));

Go

Insert

Into     Tbl1_tmp(Txt)

Select   'Adva' Union All

Select   'Beni';

Insert

Into     Tbl2_tmp(Txt,Mispar)

Select   'Carmel',10 Union All

Select   'Dan',20 Union All

Select   'Esther',30;

Go

Create Table Tavlaot(Tavla Varchar(50) Primary Key);

Go

Insert

Into    Tavlaot

Select  'Tbl1' Union All

Select  'Tbl2';

Go

Declare @Tavla Varchar(50),

        @SQL Varchar(Max);

Open Cr

Fetch Next From Cr Into @Tavla

While @@Fetch_Status=0

    Begin

    Set @SQL='';

    Select  @SQL=@SQL+','+name

    From    sys.columns

    Where   Object_Name(Object_Id)=@Tavla

            And name<>'Taarih';

    Set @SQL=Stuff(@SQL,1,1,'');

    Set @SQL='Insert Into '+@Tavla+'(Taarih,'+@SQL+') Select GetDate() Taarih,'+@SQL+' From '+@Tavla+'_tmp;';

    Set @SQL=@SQL+'Delete From '+@Tavla+'_tmp;';

    Print @SQL;

    Exec(@SQL);

    Fetch Next From Cr Into @Tavla;

    End

Close Cr;

Deallocate Cr;

Go

Declare Cr Cursor For Select * From Tavlaot;

Declare @Tavla Varchar(50),

        @SQL Varchar(Max);

Open Cr

Fetch Next From Cr Into @Tavla

While @@Fetch_Status=0

    Begin

    Set @SQL='';

    Select  @SQL=@SQL+','+name

    From    sys.columns

    Where   Object_Name(Object_Id)=@Tavla

            And name<>'Taarih';

    Set @SQL=Stuff(@SQL,1,1,'');

    Set @SQL='Insert'+Char(13)+'Into '

            +Char(9)+@Tavla+'(Taarih,'+@SQL+')'

            +Char(13)+'Select'+Char(9)+'GetDate() Taarih,'

            +Char(13)+Char(9)+Char(9)+Replace(@SQL,',',','+Char(13)+Char(9)+Char(9))

            +Char(13)+'From '+Char(9)+@Tavla+'_tmp;';

    Set @SQL=@SQL+Char(13)+Char(13)+'Delete'+Char(13)+'From'+Char(9)+@Tavla+'_tmp;';

    Print @SQL;

    Select @SQL For XML Path('');

    Exec(@SQL);

    Fetch Next From Cr Into @Tavla;

    End

Close Cr;

Deallocate Cr;

RaisError('נסיון 1',1,1);

Go

RaisError('נסיון 2',16,1);

Go

Begin Try

Print 'התחלה';

RaisError('נסיון',1,1);

Print 'סיום תקין';

End Try

Begin Catch

Print IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar)+', Error_Message()='+Cast(Error_Message() As Varchar)+', Error_Severity()='+Cast(Error_Severity() As Varchar);

Print 'סיום שגוי'

End Catch

Begin Try

Print 'התחלה';

RaisError('נסיון',16,1);

Print 'סיום תקין';

End Try

Begin Catch

Print IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar)+', Error_Message()='+Cast(Error_Message() As Varchar)+', Error_Severity()='+Cast(Error_Severity() As Varchar);

Print 'סיום שגוי'

End Catch

Print 'התחלה';

RaisError('נסיון',24,1) With Log;

Print 'סיום';

Select *

From sys.messages

Order By message_id;

Go

RaisError(19129,1,1);

Go

RaisError(14524,1,1,'Me','You');

Go

EXEC sp_AddMessage 50001,1,'פריט %s אינו קיים';

Go

RaisError(50001,1,1,'אבקת חשמל');

Go

sp_DropMessage 50001;

Go

Print '10';

RaisError(13005,1,1);

Print '20';

Begin Try

Print '110';

RaisError(13005,1,1);

Print '120';

RaisError(13005,16,1);

Print '130';

End Try

Begin Catch

Print IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar)+', Error_Message()='+Cast(Error_Message() As Varchar)+', Error_Severity()='+Cast(Error_Severity() As Varchar);

Print '999'

End Catch

Begin Try

Print '110';

Throw 50000,'שגיאה',1;

Print '120';

End Try

Begin Catch

Print IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar)+', Error_Message()='+Cast(Error_Message() As Varchar)+', Error_Severity()='+Cast(Error_Severity() As Varchar);

Print '999';

End Catch

Begin Try

Print '110';

Select Cast('X' As Int);

Print '120';

End Try

Begin Catch

Print IsNull(Object_Name(@@ProcID),'פרוצדורה לא ידועה')+': Error_Procedure()='+IsNull(Error_Procedure(),'פרוצדורה לא ידועה')+', Error_Line()='+Cast(Error_Line() As Varchar)+', Error_Message()='+Cast(Error_Message() As Varchar)+', Error_Severity()='+Cast(Error_Severity() As Varchar);

Print '999';

Throw;

End Catch

Select  *

From    (Select Row_Number() Over(Order By name) Mispar,

        name

        From sys.tables) T

Full Join (Select Row_Number() Over(Order By name) Mispar,

        name

        From sys.views) V

        On T.Mispar=V.Mispar

Full Join (Select Row_Number() Over(Order By name) Mispar,

        name

        From sys.Procedures) P

        On Coalesce(T.Mispar,V.Mispar)=P.Mispar

Full Join (Select Row_Number() Over(Order By name) Mispar,

        name

        From sys.triggers) Tr

        On Coalesce(T.Mispar,V.Mispar,P.Mispar)=Tr.Mispar;

Go

Use tempdb;

Go

If Object_Id('T1') Is Not Null Drop Table T1;

If Object_Id('T2') Is Not Null Drop Table T2;

Go

Create Table T1(I00 Int Sparse Null,

                I01 Int Sparse Null,

                I02 Int Sparse Null,

                I03 Int Sparse Null,

                I04 Int Sparse Null,

                I05 Int Sparse Null,

                I06 Int Sparse Null,

                I07 Int Sparse Null,

                I08 Int Sparse Null,

                I09 Int Sparse Null,

                I10 Int Sparse Null,

                I11 Int Sparse Null,

                I12 Int Sparse Null,

                I13 Int Sparse Null,

                I14 Int Sparse Null,

                I15 Int Sparse Null,

                I16 Int Sparse Null,

                I17 Int Sparse Null,

                I18 Int Sparse Null,

                I19 Int Sparse Null,

                I20 Int Sparse Null,

                I21 Int Sparse Null,

                I22 Int Sparse Null,

                I23 Int Sparse Null,

                I24 Int Sparse Null,

                I25 Int Sparse Null,

                I26 Int Sparse Null,

                I27 Int Sparse Null,

                I28 Int Sparse Null,

                I29 Int Sparse Null,

                I30 Int Sparse Null,

                I31 Int Sparse Null,

                I32 Int Sparse Null,

                I33 Int Sparse Null,

                I34 Int Sparse Null,

                I35 Int Sparse Null,

                I36 Int Sparse Null,

                I37 Int Sparse Null,

                I38 Int Sparse Null,

                I39 Int Sparse Null,

                I40 Int Sparse Null,

                I41 Int Sparse Null,

                I42 Int Sparse Null,

                I43 Int Sparse Null,

                I44 Int Sparse Null,

                I45 Int Sparse Null,

                I46 Int Sparse Null,

                I47 Int Sparse Null,

                I48 Int Sparse Null,

                I49 Int Sparse Null,

                I50 Int Sparse Null,

                I51 Int Sparse Null,

                I52 Int Sparse Null,

                I53 Int Sparse Null,

                I54 Int Sparse Null,

                I55 Int Sparse Null,

                I56 Int Sparse Null,

                I57 Int Sparse Null,

                I58 Int Sparse Null,

                I59 Int Sparse Null,

                I60 Int Sparse Null,

                I61 Int Sparse Null,

                I62 Int Sparse Null,

                I63 Int Sparse Null,

                I64 Int Sparse Null,

                I65 Int Sparse Null,

                I66 Int Sparse Null,

                I67 Int Sparse Null,

                I68 Int Sparse Null,

                I69 Int Sparse Null,

                I70 Int Sparse Null,

                I71 Int Sparse Null,

                I72 Int Sparse Null,

                I73 Int Sparse Null,

                I74 Int Sparse Null,

                I75 Int Sparse Null,

                I76 Int Sparse Null,

                I77 Int Sparse Null,

                I78 Int Sparse Null,

                I79 Int Sparse Null,

                I80 Int Sparse Null,

                I81 Int Sparse Null,

                I82 Int Sparse Null,

                I83 Int Sparse Null,

                I84 Int Sparse Null,

                I85 Int Sparse Null,

                I86 Int Sparse Null,

                I87 Int Sparse Null,

                I88 Int Sparse Null,

                I89 Int Sparse Null,

                I90 Int Sparse Null,

                I91 Int Sparse Null,

                I92 Int Sparse Null,

                I93 Int Sparse Null,

                I94 Int Sparse Null,

                I95 Int Sparse Null,

                I96 Int Sparse Null,

                I97 Int Sparse Null,

                I98 Int Sparse Null,

                I99 Int Sparse Null);

Create Table T2(I00 Int Null,

                I01 Int Null,

                I02 Int Null,

                I03 Int Null,

                I04 Int Null,

                I05 Int Null,

                I06 Int Null,

                I07 Int Null,

                I08 Int Null,

                I09 Int Null,

                I10 Int Null,

                I11 Int Null,

                I12 Int Null,

                I13 Int Null,

                I14 Int Null,

                I15 Int Null,

                I16 Int Null,

                I17 Int Null,

                I18 Int Null,

                I19 Int Null,

                I20 Int Null,

                I21 Int Null,

                I22 Int Null,

                I23 Int Null,

                I24 Int Null,

                I25 Int Null,

                I26 Int Null,

                I27 Int Null,

                I28 Int Null,

                I29 Int Null,

                I30 Int Null,

                I31 Int Null,

                I32 Int Null,

                I33 Int Null,

                I34 Int Null,

                I35 Int Null,

                I36 Int Null,

                I37 Int Null,

                I38 Int Null,

                I39 Int Null,

                I40 Int Null,

                I41 Int Null,

                I42 Int Null,

                I43 Int Null,

                I44 Int Null,

                I45 Int Null,

                I46 Int Null,

                I47 Int Null,

                I48 Int Null,

                I49 Int Null,

                I50 Int Null,

                I51 Int Null,

                I52 Int Null,

                I53 Int Null,

                I54 Int Null,

                I55 Int Null,

                I56 Int Null,

                I57 Int Null,

                I58 Int Null,

                I59 Int Null,

                I60 Int Null,

                I61 Int Null,

                I62 Int Null,

                I63 Int Null,

                I64 Int Null,

                I65 Int Null,

                I66 Int Null,

                I67 Int Null,

                I68 Int Null,

                I69 Int Null,

                I70 Int Null,

                I71 Int Null,

                I72 Int Null,

                I73 Int Null,

                I74 Int Null,

                I75 Int Null,

                I76 Int Null,

                I77 Int Null,

                I78 Int Null,

                I79 Int Null,

                I80 Int Null,

                I81 Int Null,

                I82 Int Null,

                I83 Int Null,

                I84 Int Null,

                I85 Int Null,

                I86 Int Null,

                I87 Int Null,

                I88 Int Null,

                I89 Int Null,

                I90 Int Null,

                I91 Int Null,

                I92 Int Null,

                I93 Int Null,

                I94 Int Null,

                I95 Int Null,

                I96 Int Null,

                I97 Int Null,

                I98 Int Null,

                I99 Int Null);

Go

Declare    @N Int=1000001;

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<1000000)

Insert Into T1

Select    Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null

From T

Where    I%@N<>0

Union All

Select    I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I,I

From T

Where    I%@N=0

Option (MaxRecursion 0);

With T As

(Select 1 I

Union All

Select I+1

From T

Where I<1000000)

Insert Into T2

Select    Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null

From T

Option (MaxRecursion 0);

Exec sp_SpaceUsed T1;

Exec sp_SpaceUsed T2;

Go

Use tempdb;

Go

Create Login MyLogin With Password='1234',Check_Policy=Off;

Go

Create User MyUser For Login MyLogin;

Go

Create Proc MyProc1 As

Select sUser_name() [sUser_name()],

       User_name() [User_name()];

Go

Grant Execute On MyProc1 To MyUser;

Go

Execute As User='MyUser';

Go

Exec MyProc1;

Go

Revert;

Go

Exec MyProc1;

Go

Create Proc MyProc2 With Execute As Owner As

Select sUser_name() [sUser_name()],

       User_name() [User_name()];

Go

Grant Execute On MyProc2 To MyUser;

Go

Execute As User='MyUser';

Go

Exec MyProc2;

Go

Revert;

Go

Exec sp_addextendedproperty 'Description','Actual employees', 'User', 'dbo', 'Table', 'Employees';

Exec sp_addextendedproperty 'Description','0=woman, 1=man, Null=unknown', 'User', 'dbo', 'Table', 'Employees', 'Column','Gender';

Go

Select Object_Name(major_id) Tbl,

       Col_Name(major_id,minor_id) Col,

       *

From   sys.extended_properties

Where  Object_Name(major_id)='Employees';

Go

Select *

From   Fn_ListExtendedProperty('Description','schema','dbo','Table','Employees','column','Gender');

Go

Select *

From   Fn_ListExtendedProperty('Description','schema','dbo','Table','Employees',Null,Null);

Go