Locate missing indices

Locate missing indices

 SELECT * FROM sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC ORDER BY D.index_handle ,[statement] 

Locate unused indices

 SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName ,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName FROM sys.indexes I WHERE OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id AND database_id = DB_ID() ) ORDER BY SchemaName ,ObjectName ,IndexName

Back Up your database using Cursor on SQL server 2008

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Find out job name from command text

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%Your_text_Here%'
GO

SQL server 2008 Script for job details

set nocount on

select 'Server'       = left(@@ServerName,20),

       'JobName'      = left(S.name,30),

       'ScheduleName' = left(ss.name,25),

       'Enabled'      = CASE (S.enabled)

                          WHEN 0 THEN 'No'

                          WHEN 1 THEN 'Yes'

                          ELSE '??'

                        END,

       'Frequency'    = CASE(ss.freq_type)

                          WHEN 1  THEN 'Once'

                          WHEN 4  THEN 'Daily'

                          WHEN 8  THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)

                          WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)

                          WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE

                          WHEN 64 THEN 'SQL Startup'

                          WHEN 128 THEN 'SQL Idle'

                          ELSE '??'

                        END,

       'Interval'    = CASE

                         WHEN (freq_type = 1)                       then 'One time only'

                         WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'

                         WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'

                         WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7

                                                       from (select ss.schedule_id,

                                                                     freq_interval,

                                                                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,

                                                                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,

                                                                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,

                                                                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,

                                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,

                                                                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,

                                                                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END

                                                                 from msdb..sysschedules ss

                                                                where freq_type = 8

                                                           ) as F

                                                       where schedule_id = sj.schedule_id

                                                    )

                         WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)

                         WHEN (freq_type = 32) then (select freq_rel + WDAY

                                                        from (select ss.schedule_id,

                                                                     'freq_rel' = CASE(freq_relative_interval)

                                                                                    WHEN 1 then 'First'

                                                                                    WHEN 2 then 'Second'

                                                                                    WHEN 4 then 'Third'

                                                                                    WHEN 8 then 'Fourth'

                                                                                    WHEN 16 then 'Last'

                                                                                    ELSE '??'

                                                                                  END,

                                                                    'WDAY'     = CASE (freq_interval)

                                                                                    WHEN 1 then ' Sun'

                                                                                    WHEN 2 then ' Mon'

                                                                                    WHEN 3 then ' Tue'

                                                                                    WHEN 4 then ' Wed'

                                                                                    WHEN 5 then ' Thu'

                                                                                    WHEN 6 then ' Fri'

                                                                                    WHEN 7 then ' Sat'

                                                                                    WHEN 8 then ' Day'

                                                                                    WHEN 9 then ' Weekday'

                                                                                    WHEN 10 then ' Weekend'

                                                                                    ELSE '??'

                                                                                  END

                                                                from msdb..sysschedules ss

                                                                where ss.freq_type = 32

                                                             ) as WS

                                                       where WS.schedule_id =ss.schedule_id

                                                       )

                       END,

       'Time' = CASE (freq_subday_type)

                        WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

                        WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'

                        WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'

                        WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'

                        ELSE '??'

                      END,

       'Next Run Time' = CASE SJ.next_run_date

                           WHEN 0 THEN cast('n/a' as char(10))

                           ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

                         END

 

   from msdb.dbo.sysjobschedules SJ

   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id

   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id

order by S.name

GO

IF (object_id('sp_ShowJobSchedules') IS NOT NULL)

  PRINT 'Procedure created.'

ELSE

  PRINT 'Procedure NOT created.'

GO

     

Sql queries for IT students

assign no 5
//check
1.create table tebranch (branch_name varchar2(10) primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));
insert into tebranch values ('sbi','kop',20000);

insert into tebranch values ('icici','kop',30000);

insert into tebranch values ('axis','pune',40000);

insert into tebranch values ('axis','pune',500);//ORA-02290: check constraint (SYSTEM.SYS_C004082) violated

select *from tebranch

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
icici kop 30000
axis pune 40000


//unique
2.create table tebranch1 (branch_name varchar2(10)  constraint tecons primary key , branch_city varchar2(10), assests number(12,2), check (assests >1000));

insert into tebranch1 values ('sbi','kop',20000);

insert into tebranch1 values ('icici','kop',30000);

insert into tebranch1 values ('axis','pune',40000);

insert into tebranch1 values ('rbi','pune',40);////ORA-02290:check constraint (SYSTEM.SYS_C004084) violated

insert into tebranch1 values ('sbi','pune',4000);//ORA-00001: unique constraint (SYSTEM.TECONS) violated


//

4.delete from tebranch where branch_name='icici';//ORA-02292: integrity constraint (SYSTEM.SYS_C004046) violated - child record found

//foreign key
5.create table teaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references tebranch1 on delete cascade);

insert into teaccount1 values(1,'sbi',5000);

insert into teaccount1 values(2,'icici',4000);

insert into teaccount1 values(3,'axis',3000);

select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
2 icici 4000
3 axis 3000

delete from tebranch1 where branch_name='icici';
1 row(s) deleted.
select * from tebranch;
BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 20000
axis pune 40000
select *from teaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 5000
3 axis 3000


//not null
6.create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

insert into stebranch values('sbi','kop',4000);//unique constraint (SYSTEM.SYS_C004096) violated

insert into stebranch values('sbi',null,2000);//cannot insert NULL into ("SYSTEM"."STEBRANCH"."BRANCH_CITY")

insert into stebranch values('sbi','kop',300);// check constraint (SYSTEM.SYS_C004095) violated


7.create table stebranch1 (branch_name varchar2(10) constraint xxxx primary key , branch_city varchar2(10) not null, assests number(12,2),
check (assests >1000));  

insert into stebranch1 values('sbi','kop',10000);

insert into stebranch1 values('icici','kop',10000);  

select *from stebranch1

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 10000
icici kop 10000

insert into stebranch1 values('sbi','kop',10000);// ORA-00001: unique constraint (SYSTEM.XXXX) violated

insert into stebranch1 values('icici',null,10000); //ORA-01400: cannot insert NULL into ("SYSTEM"."STEBRANCH1"."BRANCH_CITY")


8.//primary key
create table stebranch (branch_name varchar2(10) primary key , branch_city varchar2(10) not null, assests number(12,2), check (assests >1000));

insert into stebranch values('sbi','kop',2000);

insert into stebranch values('icici','kop',3000);

insert into stebranch values('sbi','kop',2000);//ORA-00001: unique constraint (SYSTEM.SYS_C004096) violated

select *from stebranch;

BRANCH_NAME BRANCH_CITY ASSESTS
sbi kop 2000
icici kop 3000

//foreign key

9.
create table steaccount (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch);  

insert into steaccount values(1,'sbi',3400);

insert into steaccount values(2,'icici',3400);

select *from steaccount;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 3400
2 icici 3400

insert into steaccount values(1,'sbi',3400); //ORA-00001: unique constraint (SYSTEM.SYS_C004102) violated

insert into steaccount values(2,'rbi',3400);//ORA-02291: integrity constraint (SYSTEM.SYS_C004102) violated - parent key not found

delete from stebranch where branch_name='sbi';//ORA-02292: integrity constraint (SYSTEM.SYS_C004103) violated - child record found

insert into steaccount values(null,'sbi',3400);//ORA-01400: cannot insert NULL into ("SYSTEM"."STEACCOUNT"."ACCOUNT_NUMBER")

//foreign & delete cascade
10.create table steaccount1 (account_number number(10) not null primary key, branch_name varchar2(10), balance number (12,2), check(balance>100),
foreign key (branch_name) references stebranch on delete cascade);

insert into steaccount1 values(1,'sbi',10000);

insert into steaccount1 values(2,'icici',10000);

select *from steaccount1;

ACCOUNT_NUMBER BRANCH_NAME BALANCE
1 sbi 10000
2 icici 10000

delete from steaccount1;
2 row(s) deleted.
//delete from stebranch where branch_name='sbi'
select *from steaccount1;
no data found




//character function

1.select upper(branch_name) as branch_name from tebranch;

BRANCH_NAME
SBI
ICICI
AXIS

2.select lower(branch_name) from tebranch;

LOWER(BRANCH_NAME)
sbi
icici
axis

3.select initcap(branch_name) from tebranch;

INITCAP(BRANCH_NAME)
Sbi
Icici
Axis

4.select concat(branch_name,branch_city) from tebranch;

CONCAT(BRANCH_NAME,BRANCH_CITY)
sbikop
icicikop
axispune
   select concat(branch_name,assests) from tebranch;

CONCAT(BRANCH_NAME,ASSESTS)
sbi20000
icici30000
axis40000

5.select substr(branch_name,2,3)from tebranch;

SUBSTR(BRANCH_NAME,2,3)
bi
cic
xis

6.select instr(branch_name,'i')from tebranch;

INSTR(BRANCH_NAME,'I')
3
1
3

7.select trim('i' from branch_name)from tebranch;

TRIM('I'FROMBRANCH_NAME)
sb
cic
axis

8.select length(branch_name)from tebranch;

LENGTH(BRANCH_NAME)
3
5
4

9.select lpad(branch_name,10,'xx')from tebranch;

LPAD(BRANCH_NAME,10,'XX')
xxxxxxxsbi
xxxxxicici
xxxxxxaxis
   select rpad(branch_name,10,'xx')from tebranch;

RPAD(BRANCH_NAME,10,'XX')
sbixxxxxxx
icicixxxxx
axisxxxxxx



//number fuction

1.select round(123.467,2) from dual;

ROUND(123.467,2)
123.47
   select round(assests,3)from tebranch

ROUND(ASSESTS,3)
20000
30000
40000
   select *from tebranch

2.select trunc(123.467,2) from dual;

TRUNC(123.467,2)
123.46
   select trunc(assests,2) from tebranch;

TRUNC(ASSESTS,2)
20000
30000
40000

3.select power(2,3) from dual;

POWER(2,3)
8
   select power(assests,2) from tebranch;

POWER(ASSESTS,2)
400000000
900000000
1600000000
 
4.select abs(-2) from dual;

ABS(-2)
2
   select abs(-3) from tebranch;

ABS(-3)
3
3
3
 
5.select mod(5,3) from dual;

MOD(5,3)
2
   select mod(5,3) from tebranch;

MOD(5,3)
2
2
2


//date function
1.select sysdate from dual;

SYSDATE
20-MAR-13

2.select months_between('10-mar-13', '10-july-13') from dual;

MONTHS_BETWEEN('10-MAR-13','10-JULY-13')
-4

3.select add_months('20-mar-13', 5) from dual;
or
 select add_months(sysdate, 5) from dual;

ADD_MONTHS('20-MAR-13',5)
20-AUG-13

4.select next_day('20-mar-13', 'sunday') from dual;
or
   select next_day(sysdate, 'sunday') from dual;

NEXT_DAY('20-MAR-13','SUNDAY')
24-MAR-13

5.select last_day('20-mar-13') from dual;
or
  select last_day(sysdate) from dual;

LAST_DAY('20-MAR-13')
31-MAR-13

6.select round(sysdate,'year') from dual;

ROUND(SYSDATE,'YEAR')
01-JAN-13
  select round(sysdate,'mm') from dual;
ROUND(SYSDATE,'MM')
01-APR-13

7.select trunc(sysdate,'mm') from dual;
or
  select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,'MM')
01-MAR-13

8.select to_char(sysdate,'yyyy') from dual;

TO_CHAR(SYSDATE,'YYYY')
2013

  select to_char(sysdate,'mm') from dual;

TO_CHAR(SYSDATE,'MM')
03


9.select to_date('20032012','dd-mm-yyyy') from dual;

TO_DATE('20032012','DD-MM-YYYY')
20-MAR-12

10.select to_number(marks,'99.9') from testud;