Database Engineer

Tuesday, 16 July 2013

SQL Server - Server Roles from Quackit.com

Friday, 28 June 2013

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

Thursday, 27 June 2013

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

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

PRINT 'Procedure created.'

ELSE

PRINT 'Procedure NOT created.'

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;

Friday, 5 April 2013

Basics of SQL Server 2008 R2 for DBA

List of all database with recovery modes and version information.

'--- SQL 2005

SELECT NAME,COMPATIBILITY_LEVEL,RECOVERY_MODEL_DESC,STATE_DESC FROM SYS.DATABASES

--- SQL 2000

SELECT NAME,CMPTLEVEL,DATABASEPROPERTYEX(NAME,'RECOVERY')AS RECOVERYMODEL,

DATABASEPROPERTYEX(NAME,'STATUS') AS STATUS FROM SYSDATABASES

------------------------------------------------------------------------------------------------------------------------------------------

List database Names with Physical location where the data files reside.

--- SQL 2005
SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,NAME,TYPE_DESC,PHYSICAL_NAME FROM SYS.MASTER_FILES
--- SQL 2000
SELECT DB_NAME(DBID) AS DATABASENAME,NAME,FILENAME FROM MASTER.DBO.SYSALTFILES

e='m 2 n b ��m H p ;margin-bottom:.0001pt;line-height: normal'>How to retrieve the configuration paramaters in SQL Server 2005?

'SELECT * from sys.configurations order by NAME

SP_CONFIGURE 'show advanced options',1

RECONFIGURE with OVERRIDE

SP_CONFIGURE

List of all database and its last backup taken on SQL Server .

SELECT db.name,case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),

MAX(b.backup_finish_date)) end AS last_backup_finish_date

FROM sys.databases db

LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D'

WHERE db.database_id NOT IN (2) GROUP BY db.name

ORDER BY 2 DESC

Database Engineer

Tuesday, 16 July 2013

SQL Server - Server Roles from Quackit.com

Friday, 28 June 2013

Locate missing indices

Locate missing indices

Locate unused indices

Thursday, 27 June 2013

Back Up your database using Cursor on SQL server 2008

Find out job name from command text

SQL server 2008 Script for job details

Sql queries for IT students

Friday, 5 April 2013

Basics of SQL Server 2008 R2 for DBA

Check if column exists or not in the SQL server

Total Pageviews

Online Visitors