Developed the script to get installed SQL Server Instance on a Server. To run this script user should have admin privileges or minimum permission to read OS registry on the server.
|
DECLARE @Registry_Value VARCHAR(1000) DECLARE @SQL_In AS
INSERT EXECUTE @Registry_Value OUTPUT
SELECT |
It is really a herculean task to have a consolidated report for all the jobs status for one or more server. My much time is saved by this report
Steps to Create Report
Configure Database Mail Profile, which will be used to send mail.
USE [msdb]
GO
/****** Object: Table [dbo].[JobHistory] ******/
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_PADDING
ON
GO
CREATE
TABLE [dbo].[ServerJobHistory](
[Job_ID] [uniqueidentifier] NOT
NULL,
[Job_Name] [sysname] NOT
NULL,
[Run_Date] [int] NOT
NULL,
[Run_Time] [int] NOT
NULL,
[Run_Duration] [int] NOT
NULL,
[Job_Outcome] [varchar](100)
NOT
NULL,
[Step_Outcome] [varchar](250)
NOT
NULL
)
ON [PRIMARY]
GO
SET
ANSI_PADDING
OFF
GO
| USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_LoadJobHistory] Script Date: 03/07/2011 17:48:23 ******/ SET GO SET GO ALTER AS BEGIN SET DECLARE @JobID UNIQUEIDENTIFIER DECLARE @JobName SYSNAME DECLARE @RunDate INT DECLARE @RunTime INT DECLARE @RunDuration INT DECLARE @RunStatus INT DECLARE @FailedSteps INT DECLARE @JobOutcome VARCHAR(100) DECLARE @StepOutcome VARCHAR(250) DECLARE JobHistoryCursor CURSOR SELECT J.job_id, J.[name], JH.[run_date], JH.[run_time], ((JH.[run_duration]/10000)*3600)
JH.[run_status] FROM dbo.sysjobhistory JH JOIN dbo.sysjobs J ON J.job_id = JH.job_id WHERE JH.[job_id] IN SELECT [job_id] FROM dbo.sysjobs ) AND ( SELECT FROM dbo.ServerJobHistory IJH WHERE IJH.Job_ID = JH.[job_id] ) AND JH.[step_id] = 0 OPEN JobHistoryCursor FETCH WHILE BEGIN SELECT @FailedSteps = FROM dbo.sysjobhistory H WHERE H.job_id = @JobID AND H.run_date = @RunDate AND H.run_time/100 = @RunTime/100 AND H.step_id > 0 AND H.run_status = 0 IF (@RunStatus = 0) SELECT @JobOutcome = ELSE SELECT @JobOutcome = ELSE SELECT @JobOutcome = ELSE SELECT @JobOutcome = ELSE SELECT @JobOutcome = INSERT VALUES (@JobID, @JobName, @RunDate, @RunTime, @RunDuration, @JobOutcome, @StepOutcome) FETCH END CLOSE JobHistoryCursor DEALLOCATE JobHistoryCursor END |
| USE [msdb] GO SET GO SET GO ALTER AS BEGIN DECLARE @tableHTML NVARCHAR(MAX) SET @tableHTML = N’<html><body><Font Face=”Verdana” Size=4><b>Server Job History Report</b></Font><p>’ N’<Font Face=”Verdana” Size=2>Report generated on: ‘ N’<Font Face=”Verdana” Size=2>This report shows yesterday”s history only.</Font><p>’ N’<table border=”1″width=”100%”><tr bgcolor=”Black”>’ N’<td><Font Face=”Verdana” Size=2 Color=”White”><b>Job Name</b></Font></td>’ N’<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Date</b></Font></td>’ N’<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Time</b></Font></td>’ N’<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Duration (sec)</b></Font></td>’ N’<td><Font Face=”Verdana” Size=2 Color=”White”><b>Last Run Status</b></Font></td>’ cast td = td = td = td = Case Job_Outcome WHEN WHEN ELSE END FROM dbo.ServerJobHistory WHERE Run_Date = ORDER FOR SET @tableHtml = SELECT @tableHtml IF (ISNULL(@tableHtml, BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients=‘xyz@xyz.com’, @subject=‘Job History Report’, @body=@tableHTML, @body_format=‘HTML’ END END |
Report Looks as below
I have seens this common question on few SQL Server Forum that how can i get the last execution date for Stored Procedur or when was the stored procedure exedcuted last.
Now How to achive this?
Every SQL Stament sent to SQL Server is PARSED,COMPILEd and EXECTED.SQL Server maintains QUERY PLAN for each and every statement and keeps it in the system tables.
Which we can query using DMV’s.Query Plans are flushed out if SQL Server Service gets started manulally or automatically(In cluster mode)/runs update stats command / reindexing / usinf DBCC CACHE Clean Commands.
To get last execution date of the stored procedure if SQL Server has the Query Plan for it.
SELECT
qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text like ‘%USP_CDS_GetUserDetails%’
Script :Grant Permisison on User Object to User (Read Only Permission)
The will help to generate Grant Select,Execute & View permission to user on dataabse objects.We use this script when we have to give read only permission to users on prodcution databases.
| —Select Permisison on User Table— DECLARE @login varchar(50)
SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘U’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Execute Permission on User Stored Procedure—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘P’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Select Permission on User View—– GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE = ‘V’ AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Select Permission on User Function (Table-Valued-Function)—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE IN (‘TF’) AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Select Permission on User Function (Scalar-Valued-Function)—- GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login FROM sys.objects so INNER JOIN sys.schemas sc ON so.schema_id= sc.schema_id WHERE TYPE IN (‘FN’) –AND so.NAME NOT LIKE ‘SYNC%’ SELECT * FROM @tables —Grant View Defination on User Types— GO DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT VIEW DEFINITION ON TYPE :: ‘ + ‘dbo’ +’.’ + st.NAME + ‘ TO ‘+@login FROM sys.types st where st.schema_id=1 SELECT * FROM @tables |
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated
CREATE VIEW <View Name>
WITH SCHEMABINDING
AS
SELECT Col1,Col2,Col3
FROM dbo.tablename
create unique clustered index indexname on dbo.viewname (colnames)
We have a testing envornment with more then 35 databases.Every week we need to restore all the database with recent backup of prodcution databases. So we came up with dynamic sql solution.We keep all the backup file at one location and script checks the database name and overrite the database using respective backup file. Once the restore is done script truncate the log file. Code will check the existing database file location and will keep the fill to the same location on restoring the database. Version Microsoft Feb 9 2007 22 Microsoft Corporation Enterprise Edition SQL Server 2005 - 9.00.3042.00 (Intel X86) :47:07 Copyright (c) 1988-2005 on Windows NT 5.2 (Build 3790: Service Pack 2)
USE [master]
GO
--****** Object: StoredProcedure [dbo].[Usp_dba_RestoreDatabase]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Usp_dba_RestoreDatabase]
AS
BEGIN
SET NOCOUNT ON
DECLARE @DBName varchar(255)
DECLARE @vBackupFileName varchar(255)
DECLARE @vNewPath varchar(1000)
DECLARE @vFileName varchar(100)
DECLARE @BackupFilePath varchar(1000)
DECLARE @SQL varchar(max)
DECLARE @dated varchar(50)
DECLARE @vCount int
DECLARE @vNumDBs int
DECLARE @LogDFileName varchar(max)
DECLARE @LogDFilePath nvarchar(max)
DECLARE @LogLFileName varchar(max)
DECLARE @LogLFilePath nvarchar(max)
DECLARE @vErrorString varchar(1000)
--***********--
--Error log variables
DECLARE @cmd varchar(500), @var varchar(500),@ServerName varchar(50),
@ErrorMessage varchar(100),@filename varchar(100)
---------------
DECLARE @vDBDaFileList Table
(
DBDaFile Varchar(256) ,DataFilePath varchar(max)
)
DECLARE @vDBDLList Table
(
DBLgFile Varchar(256) , LogFilePath varchar(max)
)
DECLARE @vDBList Table
(
DBID INT NOT NULL IDENTITY(1, 1),DBakName Varchar(256)
)
DECLARE @vDBHList Table
(
DBID INT NOT NULL IDENTITY(1, 1),
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed tinyint,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
CodePage smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier NULL
)
-----Insert backupfile name from the backup file location-----
Insert Into @vDBList
exec xp_cmdshell 'dir D:\DataBackup\*.bak /B /O'
--------------------------------------------------------------
Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
DELETE FROM @vDBList where DBakName is NULL
SELECT @vBackupFileName = DBakName From @vDBList Where DBID = @vCount and DBakName is not NULL
SET @BackupFilePath=''''+'D:\DataBackup\'+@vBackupFileName+''''
SET @SQL='RESTORE HEADERONLY FROM DISK= '+ @BackupFilePath
--Print @SQL
--SELECT * FROM sys.master_files
INSERT INTO @vDBHList
exec (@SQL)
DELETE FROM @vDBHList WHERE DatabaseName is NULL or DatabaseName=''
DELETE FROM @vDBHList WHERE DatabaseName in ('master','model','msdb')
DELETE FROM @vDBHList WHERE DatabaseName not in (Select name from sys.databases)
--SELECT DBID,databasename FROM @vDBHList --where databasename is not null or databasename=''
SET @DBName=''
SELECT @DBName=databasename from @vDBHList where DBID=@vCount ---and databasename is not null
IF @DBName <>''
BEGIN
SET @SQL='SELECT name,physical_name FROM sys.master_files where
database_id=db_id('''+@DBName+''') and type_desc=''ROWS'''
INSERT into @vDBDaFileList exec (@SQL)
select @LogDFileName=DBDaFile ,@LogDFilePath=DataFilePath from @vDBDaFileList
SET @SQL='SELECT name,physical_name FROM sys.master_files
where database_id=db_id('''+@DBName+''') and type_desc=''LOG'''
INSERT into @vDBDLList exec (@SQL)
select @LogLFileName=DBLgFile , @LogLFilePath=LogFilePath from @vDBDLList
SET @SQL='RESTORE DATABASE ['+@DBName +
'] FROM DISK ='+ @BackupFilePath+
' WITH FILE = 1, '+
'MOVE '+''''+@LogDFileName+''''+ 'TO '+ '''' + @LogDFilePath +''''+', '+
'MOVE '+''''+@LogLFileName+''''+ 'TO '+ '''' + @LogLFilePath +''''+', '+
'NOUNLOAD, REPLACE, STATS = 10'
Print '----------Restore started for Database['+@DBName+']---------------'
--Print @SQL
exec (@SQL)
Print '----------Restore Completed for Database['+@DBName+']--------------'
Print ' '
Print'-----------Truncate Log for Database['+@DBName+'] started---------------'
SET @SQL='BACKUP LOG '+@DBNAME+ ' WITH TRUNCATE_ONLY'
exec (@SQL)
SET @SQL='USE ['+ @DBName +'] ' +'DBCC SHRINKFILE(2,1) with mesg'
Print '----------Truncate Log for Database['+@DBName+'] completed--------------'
Print ' '
END
SET @vCount = @vCount + 1
END
SET NOCOUNT OFF
END
|
Drop Foreign key script will generate drop script for all foreign keys within a database.
SET NOCOUNT ON
DECLARE @FK_Name VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @SC_Name VARCHAR(10)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @vCount Int
DECLARE @vNumDBs Int
DECLARE @DB_NAME VARCHAR(500)
DECLARE @vFKeyList Table
(
SLID INT NOT NULL IDENTITY(1, 1),
FK_Name VARCHAR(100),
FK_TableName VARCHAR(100),
SC_Name VARCHAR(10)
)
INSERT INTO @vFKeyList
SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,
schema_name(f.schema_id) as SC_ID
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
Select @FK_Name = FK_Name,@FK_TableName=FK_TableName,
@SC_Name=SC_Name From @vFKeyList Where SLID = @vCount
SET @DB_NAME=DB_NAME()
SET @SQL=''
SET @SQL='USE [' + @DB_NAME + '] ALTER TABLE ['+ @SC_Name +'].['+ @FK_TableName + '] ' +
'DROP CONSTRAINT ['+@FK_Name+']'
Print @SQL
Print 'GO'
Set @vCount = @vCount + 1
End
SET NOCOUNT OFF
Sample Result
GO
USE [easy] ALTER TABLE [dbo].[ext_eri_dig_adn] DROP CONSTRAINT [FK_exteda_exte]
GO
The below script will generate create script for all foreign keys in a database. Variable @NoCheckVal will serve the purpose if u wants to create the script with NO CHECK option.
Create the stored procedure in the target database.
CREATE PROCEDURE Usp_dba_GenFKScript (@NoCheckVal char(2))
AS
BEGIN
SET NOCOUNT ON
DECLARE @FK_Name VARCHAR(100)
DECLARE @FK_TableName VARCHAR(100)
DECLARE @FK_ColumnName VARCHAR(50)
DECLARE @RF_TableName VARCHAR(100)
DECLARE @RF_ColumnName VARCHAR(50)
DECLARE @SC_Name VARCHAR(10)
DECLARE @NoCheck Char(2)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @vCount Int
DECLARE @vNumDBs Int
DECLARE @DB_NAME VARCHAR(500)
DECLARE @vFKeyList Table
(
SLID INT NOT NULL IDENTITY(1, 1),
FK_Name VARCHAR(100),
FK_TableName VARCHAR(100),
FK_ColumnName VARCHAR(50),
RF_TableName VARCHAR(100),
RF_ColumnName VARCHAR(50),
SC_Name VARCHAR(10)
)
SET @DB_NAME=DB_NAME()
INSERT INTO @vFKeyList
SELECT f.name AS FK_Name,OBJECT_NAME(f.parent_object_id) AS FK_TableName,COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS FK_ColumnName,OBJECT_NAME (f.referenced_object_id) AS RF_TableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS RF_ColumnName,
schema_name(f.schema_id) as SC_ID
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Set @vNumDBs = @@RowCount
Set @vCount = 1
If @NoCheckVal is null
BEGIN
SET @NoCheck=”
END
ELSE
SET @NoCheck=@NoCheckVal
While @vCount <= @vNumDBs
Begin
Select @FK_Name=FK_Name,@FK_TableName=FK_TableName,
@FK_ColumnName=FK_ColumnName, @RF_TableName=RF_TableName,
@RF_ColumnName=RF_ColumnName,@SC_Name=SC_Name
FROM @vFKeyList where SLID= @vCount
SET @SQL=’USE [' + @DB_NAME + '] ALTER TABLE ['+ @SC_Name +'].['+ @FK_TableName +'] WITH ‘ + @NoCheck +’CHECK ADD CONSTRAINT ['+@FK_Name+'] FOREIGN KEY(['+@FK_ColumnName+
']) REFERENCES ['+@SC_Name+'].['+@RF_TableName+'] (['+@RF_ColumnName+'])’
Print @SQL
Print ‘GO’
SET @SQL=’USE [' + @DB_NAME + '] ALTER TABLE ['+ @SC_Name +'].['+ @FK_TableName +'] CHECK CONSTRAINT ['+@FK_Name+']‘
Print @SQL
Print ‘GO’
Set @vCount = @vCount + 1
End
SET NOCOUNT OFF
END
Script Sample Without NO Check option
EXECUTE Usp_dba_GenFKScript ”
USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH CHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])
GO
USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]
GO
Script Sample With No Check option
EXECUTE Usp_dba_GenFKScript ‘NO’
USE [easy] ALTER TABLE [dbo].[ext_acdposition] WITH NOCHECK ADD CONSTRAINT [FK_extacdp_swext] FOREIGN KEY([switch_extension]) REFERENCES [dbo].[switch_extension] ([switch_device])
GO
USE [easy] ALTER TABLE [dbo].[ext_acdposition] CHECK CONSTRAINT [FK_extacdp_swext]
GO
msufian