I constantly keep querying the Database sometimes for checking the metadata in the DatawareHouse. Lately I realised that have been executing the same long sql statements everyday. So is there a better way of Organising all my Favourite SQL statments or procedures ? This is what I ended up with. Create a Table The table [...]
Mar
31
2009
31
2009
Mar
29
2009
29
2009
DayLight Savings and Sql Agent
Ran a Job yesterday to check how the SQL agent Handles the DayLight Savings Created the following Table Created the Following procedure to enter the current Date and Ran the Job every minute from March 28th 2009 22:50 to Mar 29th 2009 3:00 AM Results: There was no 01:00 AM on March 29th !!!!. So [...]
Mar
17
2009
17
2009
Backup Report on SQL SERVER Databases
The Following Script will output the List of Databases in the SQL server and gets the Last Date the Databases were backed up select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,max(backup_finish_date) as ‘Last_backup_date’ from msdb..backupset Where database_name in (select name from master..sysdatabases) and server_name = @@servername group by server_name,database_name order by Last_backup_date DESC
Mar
11
2009
11
2009
When the Procedures were Last Modified
SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear, modify_date, datepart(dy,modify_date) as ModificationDayOfYear FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = ‘P’ order by datepart(yyyy,modify_date) desc, datepart(dy,modify_date) desc, name;
Mar
11
2009
11
2009
Proper Case in Sql Server
Here is the Sql function that will Proper Case any column in a table set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO create function [dbo].[ProperCase](@Text as varchar(8000)) returns varchar(8000) as begin declare @Reset bit; declare @Ret varchar(8000); declare @i int; declare @c char(1); select @Reset = 1, @i=1, @Ret = ” while (@i <= len(@Text)) select [...]
