How to drop all tables, all views, and all stored procedures from a SQL 2005 DB...

This is a follow-up to the blog entry from Jasper Jugan. This is a modification to the script to allow for schema specific deletes of the SP’s, Views and I added Functions.   create procedure usp_DropSPFunctionsViews as   — variable to object name declare @name varchar(1000) — variable to hold object type declare @xtype varchar(20)…

6

SQL Server Function to merge a date with a time

I use this when I need to join two fields. One has a date, the other has a time.   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_MergeDate2Time]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_MergeDate2Time] GO CREATE FUNCTION [util].[uf_MergeDate2Time]( @date DATETIME, @time DATETIME ) RETURNS DATETIME WITH EXECUTE AS…

2

SQL Server Function to get the Hour of the Year

This function returns an integer of the hour of the year passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfYear]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHourOfYear] GO CREATE FUNCTION [util].[uf_GetHourOfYear]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…

1

SQL Server Function to get the Hour of the Month

This function returns an integer of the hour of the month passed as a variable. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHourOfMonth]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHourOfMonth] GO CREATE FUNCTION [util].[uf_GetHourOfMonth]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…

1

SQL Server Function to return half year number of days.

This function returns an integer of the number of days in the half year.   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetHalfYearDays]’) AND type IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION [util].[uf_GetHalfYearDays] GO CREATE FUNCTION [util].[uf_GetHalfYearDays]( @date DATETIME ) RETURNS INTEGER WITH EXECUTE AS CALLER AS /********************************************************************************************************** * UDF Name: *…

1

SQL Server Function to Return DateName

This function will return the date name for a given date time. 1: USE [DW_SharedDimensions]; 2: GO 3: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetDateName]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) 4: DROP FUNCTION [util].[uf_GetDateName] 5: GO 6: CREATE FUNCTION [util].[uf_GetDateName]( 7: @date DATETIME 8: ,@includetime bit 9: ,@timeformat tinyint)…

1

SQL Server Function to Return Date from Numeric Value

This function will return a datetime for a numeric value. This is using the 1900 date system, the same as used by Excel. See http://office.microsoft.com/en-us/excel/HP100791811033.aspx for more examples.   1: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetDateFromNumeric]’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) 2: DROP FUNCTION [util].[uf_GetDateFromNumeric] 3: GO 4:  …

1

SQL Server Function to Return Numeric Value for DateTime

Technorati Tags: SQL Server, SQL, Function, Intellectually Constipated This function will return a numeric value for a given date. This is using the 1900 date system, the same as used by Excel. See http://office.microsoft.com/en-us/excel/HP100791811033.aspx for more examples.   1:   2: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[util].[uf_GetNumericDate]’) AND type in (N’FN’, N’IF’,…

2