Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server
Tip/Trick

Some Important SQL Queries

Rate me:
Please Sign up or sign in to vote.
4.64/5 (14 votes)
6 Nov 2011CPOL 38.6K   32   4
Some Important SQL Queries
  1. List all the databases on SQL Servers.

    You can do it by following System Stored Procedures and queries:
    SQL
    EXEC sp_databases
    
    EXEC sp_helpdb
    
    SELECT name
    FROM sys.databases
    
    SELECT name
    FROM sys.sysdatabases


  2. List all Tables in the database on SQL Servers.

    You can find out all tables within a database by the following query:
    SQL
    SELECT *
    FROM sys.Tables
    
    SELECT * FROM information_schema.tables
    
    SELECT * FROM sysobjects WHERE xtype='U'


    Here is a list of other object types you can search for as well:

    C: Check constraint
    D: Default constraint
    F: Foreign Key constraint
    L: Log
    P: Stored procedure
    PK: Primary Key constraint
    RF: Replication Filter stored procedure
    S: System table
    TR: Trigger
    U: User table
    UQ: Unique constraint
    V: View
    X: Extended stored procedure

  3. List all Stored Procedures in the database on SQL Servers.

    You can find out all Stored Procedures within a database by the following query:

    SQL
    SELECT name
    FROM sys.objects
    WHERE type = 'P'
    If you want to find it within a period then;
    
    SELECT name
    FROM sys.objects
    WHERE type = 'P'
    AND DATEDIFF(D,create_date, GETDATE()) <5


    The above query will find out all Stored Procedures created within 5 days.

  4. List all User Defined Functions in the database on SQL Servers.

    You can find out all User Defined Functions within a database by the following query:

    SQL
    SELECT *
    FROM sys.objects
    WHERE type_desc LIKE '%FUNCTION%';

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Max Bupa Health Insurance Company Ltd.
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalsample sql queries pivot,cte,rank ,etc Pin
Member 1357335812-Dec-17 21:01
Member 1357335812-Dec-17 21:01 
QuestionRefer the Following Link ... for most important SQL Queries in various databases Pin
Rajasekhar Burepalli17-Jun-13 21:41
Rajasekhar Burepalli17-Jun-13 21:41 
GeneralMy vote of 2 Pin
MehtaVikas25-Jun-12 21:15
MehtaVikas25-Jun-12 21:15 
GeneralRe: My vote of 2 Pin
uditshukla9-Sep-12 23:45
uditshukla9-Sep-12 23:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.