Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Some general purpose SQL queries

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 156
    Comment on it

    Some general purpose SQL queries syntax

     

    1) To get text of stored procedures, views and triggers  

    sp_helptext 'Object_Name'

     

    2) To get all stored procedures of  a related database  

    Select * from sys.objects where type='P'

     

    3) To get all tables of a related database   

    Select * from sys.objects where type='U'
    
    Select * from sys.tables

     

    4) To get all views of  a related database   

    Select * from sys.objects where type='V'

     

    5) To get all functions of  a related database   

    Select * from sys.objects where type='FN'

     

    6) To get list of all database  

     Exec sp_helpdb

     

    7) To get all stored procedures of a related table  

    SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE  c.TEXT LIKE '%Table_Name%' AND o.xtype='P'

      

    8) Enable/ Disable a particular trigger of a table

    Alter table Table_Name Enable Trigger Trigger_Name
    
    Alter table Table_Name Disable Trigger Trigger_Name

     

    9) Enable/ Disable all triggers of a table   

    Alter table Table_Name Enable Trigger All
    
    Alter table Table_Name Disable Trigger All

     

    10) To get list of all stored procedures created in last N days 

    SELECT name,create_date FROM sys.objects WHERE type='P' AND DATEDIFF(D,create_date,GETDATE())< N 

     

    11)  To get list of all stored procedures modified in last N days 

    SELECT name,modify_date FROM sys.objects WHERE type='P' AND DATEDIFF(D,modify_date,GETDATE())< N 

     

    12) To swap the values of two columns of a table  

    Update Table_Name set Column1=Column2 , Column2 = Column1

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: