Important SQL Queries Examples

·         Queries for Database Details:

·         To know Details about database and tables

sp_helpdb

o   Displays Details about all the databases.

select *from master.dbo.sysdatabases

o   It shows List of all databases.

sp_help employee

o   Displays the description about the tables

select *from sysobjects where type='u'

o   It shows How many indexes created on a table and their details.

sp_helpindex employee

o   shows the details about all users.

sp_who

·         Queries Related to Indexes

o   creates index as indexempname on empname column.

    create index indexempname on Employee(EmployeeCode)

o   create index as indexnm on empname which doesnt allow duplicate values.

create unique index indexempnm on Employee(EmployeeCode)

o   It drops the index indexempnm.

drop index indexempname on Employee 

  

o   Creation of index on two columns.

     create index indexcommon on

Employee(EmployeeCode,DeptCode) 

o   Assigning particular index for retrieving records.

    select EmployeeCode,DeptCode from Employee with(Index=indexempnm) 

o   To see the Details about indexes.

        exec sp_helpindex employee