IMPORTANT SQL QUERY

Important SQL Query

TRIGGER
1) Create a basic sql server 2005 trigger to send email alerts

http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/

CREATE TABLE dbo.inventory
(
item varchar(50),
price money
)
GO

CREATE TRIGGER expensiveInventoryMailer
ON dbo.inventory AFTER INSERT AS

DECLARE @price money
DECLARE @item varchar(50)

SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)

IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO

Backup Database
Description: Database backups one of the most important aspects SQL Server disaster recovery planning for any production system. Backups may be used to 

provide a means of recovery to a point-in-time when the database was last operational.

Types of backups in SQL Server

1) Full Backup
2) Differential Backup
3) Transaction Log Backup
4) File(s) and Filegroup(s) backup


1) FULL Backup
Syntax
BACKUP DATABASE [Databasename]
TO DISK = [Path: Where you want to Store the Backup]
WITH INIT

Real Time
BACKUP DATABASE Employee
TO DISK = 'c:\backups\Employee.bak'
WITH INIT

2) Differential Backup
Syntax
BACKUP DATABASE {Database Name}
TO DISK = {Path: Where you want to store the Database}
WITH INIT, DIFFERENTIAL

Real Time
BACKUP DATABASE Employee
TO DISK = 'c:\backups\Employee_diff.bak'
WITH INIT, DIFFERENTIAL

3) Transaction Log Backup 
Syntax
BACKUP LOG {Database Name}
TO DISK = {Path: Where you want to store the Database}
WITH INIT

Real Time
BACKUP LOG Employee
TO DISK = 'c:\backups\Employee_log_1.bak'
WITH INIT

4) File(s) and Filegroup(s) backup 

Syntax  

BACKUP Database {databasename} FILE={filename},
FILEGROUP={filegroup} TO {device}

View SQL Server Agent job history
EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL, 'job name', @MODE = N'SEM'

To Find out the Drive Space 

EXEC master.dbo.xp_fixeddrives

Result

Find out how many row are there in Employee database

SELECT tn.name as [TableNames] ,rc.rows as [TblRowCounts]

FROM sysobjects tn

INNER JOIN sysindexes rc ON (tn.id = rc.id)

WHERE tn.xtype = 'u'

AND rc.indid <>
 

order by TableNames
 
 
Result
 

 
To find out All Database in the server
 
select name from master..sysdatabases select name from sys.databases
 
Result

 
Size of the database
 
select * from sysfiles
 
 
To Get the Particular Tables Size
 
EXEC sp_spaceused 'Employee.dbo.EmployeeInfo'
 
Result
 
 
SELECT c.name AS Sysadmin_Server_Role_Members FROM sys.server_principals a    INNER JOIN sys.server_role_members b  ON a.principal_id = b.role_principal_id  AND a.type = 'R' AND a.name ='sysadmin'   INNER JOIN sys.server_principals c     ON b.member_principal_id = c.principal_id
 
 


Query to Get List of System Tables

 select * from Sys.Objects where Type='s' 

Query to Get List of User Tables

 select * from Sys.Objects where Type='u' 

Query to Get List of Store Procedures

select * from Sys.Objects where Type='p'
select name,object_id,create_date from Sys.Objects where Type='p'

Query to Get List of Scalar Functions

select * from Sys.Objects where Type='fn'

select Name,object_ID,type_desc,create_date,modify_date from Sys.Objects where Type='fn' 
 

Query to Get List of Table Valued Functions
select * from Sys.Objects where Type='tf'

Query to Get List of Primary Keys 
select * from Sys.Objects where Type='PK'
select name,object_id,create_date,modify_date from Sys.Objects where Type='PK' 
 
Query to Get List of Unique Keys 
select * from Sys.Objects where Type='uq' 

select name,object_id,create_date,modify_date from Sys.Objects where Type='uq' 

Query to Get List of Forgien Keys 
select * from Sys.Objects where Type='f'
select name,object_id,create_date,modify_date from Sys.Objects where Type='F' 

Query to Get List of views
select * from Sys.Objects where Type='v'
select name,object_id,create_date,modify_date from Sys.Objects where Type='V' 

Query to Get List of Triggers
select * from Sys.Objects where Type='tr' 

Query to Get List of Internal Tables
select * from Sys.Objects where Type='it'
select name,object_id,create_date,modify_date from Sys.Objects where Type='it' 

To get SQL server version
SELECT @@VERSION

To find all table names which do not occur in TSQL
code(views, SPs, functions), use this statement
select t.name, sys.objects.name foundin, sys.objects.type_desc
from sys.objects t
left outer join
sys.sql_modules
inner join sys.objects on sys.objects.object_id = sys.sql_modules.object_id
on sys.sql_modules.definition like '%' + t.name + '%'
where t.type = 'U'
and sys.objects.name is null
order by t.name, type_desc, foundin

Find out the Tables Modified in a SQL Server Database in the Last ‘N’ Days

USE Employee
GO
SELECT name AS [TableName],
SCHEMA_NAME(schema_id) AS [Schema],
modify_date as [ModifyDate]
FROM sys.objects
WHERE modify_date > GETDATE() - 15 -- 'N' days
and type_desc = 'USER_TABLE'
order by ModifyDate desc -- Descending order
-- order by ModifyDate asc -- Descending order
GO

Find Partition Range in a SQL Server 2005/2008 PartitionTable

SELECT psch.name as PartitionScheme,
prng.value AS ParitionValue,
prng.boundary_id AS BoundaryID
FROM sys.partition_functions AS pfun
INNER JOIN sys.partition_schemes psch ON pfun.function_id = psch.function_id
INNER JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id
WHERE pfun.name = 'TransactionRangePF1'

---

Find Partition Information in a SQL Server 2005/2008 Table using T-SQL

SELECT DISTINCT part.object_id as ObjectID,
OBJECT_NAME(part.object_id) as TableNm,
idx.name as IndexNm,
idx.type_desc as IndexType,
psch.name as PartitionScheme,
pfun.name as FunctionName
FROM sys.partitions part
INNER JOIN sys.indexes idx
ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
INNER JOIN sys.data_spaces dsp
ON idx.data_space_id = dsp.data_space_id
INNER JOIN sys.partition_schemes psch
ON dsp.data_space_id = psch.data_space_id
INNER JOIN sys.partition_functions pfun
ON psch.function_id = pfun.function_id

---

Find Constraints of All Tables in a SQL Server 2005/2008 Database

USE Employee
GO
SELECT OBJECT_NAME(object_id) as [Constraint],
OBJECT_NAME(parent_object_id) AS [Table],
type_desc AS [Constraint Type],
create_date AS [Creation Date]
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
ORDER BY [Table]
GO

---

Display List of All Databases with their Sizes in SQL Server 2005/2008

SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage
FROM
(
SELECT sysMas.database_ID, sysMas.size, SUM(size)  as DBSize8KBPage
FROM sys.master_Files sysMas
GROUP BY sysMas.DataBase_ID, sysMas.size
)
temp
INNER JOIN
Sys.Databases sysDa
on temp.Database_ID = sysDa.DataBase_ID

---

List down the DataType and Size Of all Columns of
All Tables in a SQL Server 2005/2008 Database

USE Employee;
GO
SELECT
OBJECT_NAME(col.OBJECT_ID) as [TableName],
col.name AS [ColName],
SCHEMA_NAME(typ.schema_id) AS Type_schema,
typ.name AS Type_name,
col.Max_length
FROM sys.columns AS col
JOIN sys.tables AS tbl on col.object_id =  tbl.object_id
JOIN sys.types AS typ ON col.user_type_id = typ.user_type_id
--ORDER BY [TableName];
ORDER BY [ColName]
GO

---

Creating a Self-Signed Certificate in SQL Server 2005/2008

USE Employee;
CREATE CERTIFICATE SocialCert
ENCRYPTION BY PASSWORD = 'q35RED6gg577gghy53FF'
WITH SUBJECT = 'ID CERTIFICATE',
EXPIRY_DATE = '12/31/2010';
GO

---