Click here to Skip to main content
Click here to Skip to main content

Some Important SQL Queries

, 6 Nov 2011
Rate this:
Please Sign up or sign in to vote.
Some Important SQL Queries
  1. List all the databases on SQL Servers.
     
    You can do it by following System Stored Procedures and queries:
    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:
    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:
     
    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:
     
    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)

About the Author

Mani Bhushan
Software Developer Max Bupa Health Insurance Company Ltd.
India India
No Biography provided
Follow on   Twitter

Comments and Discussions

 
QuestionRefer the Following Link ... for most important SQL Queries in various databases PinmemberRajasekhar Burepalli17-Jun-13 21:41 
GeneralMy vote of 2 Pinmemberrscrbv25-Jun-12 21:15 
Too less information
GeneralRe: My vote of 2 Pinmemberuditshu9-Sep-12 23:45 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 6 Nov 2011
Article Copyright 2011 by Mani Bhushan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid