How to Rename SQL Server Cluster Virtual Server Name(Network name)

How to Rename SQL Server Cluster Virtual Server Name(Network name) Rename a SQL Server Failover Cluster Instance http://msdn.microsoft.com/en-us/library/ms178083.aspx The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change…


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)…


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…


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: *…


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: *…


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: *…


SQLCMD and the power of the little r

I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility.  I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD. sqlcmd [{ { -U login_id [ -P password ] } | –E trusted…


SQL Server Data Types Test Tables

These tables can be used for testing various data types of SQL Server. You can extend as needed. SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,QUOTED_IDENTIFIER, ANSI_NULLS ON; GO   — Create XML Schema Collection CREATE XML SCHEMA COLLECTION PartitionMapSchema AS N'<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”> <xs:element name=”Partition_Map”> <xs:complexType> <xs:sequence> <xs:element maxOccurs=”unbounded” name=”Value” type=”xs:date” /> </xs:sequence>…


Column Information View for SQL Server

Here is a view that contains column information. This is useful if you need to create a DDL statement for a table. CREATE VIEW dbo.vColumnInfo AS SELECT tbl.name AS [Table_Name], SCHEMA_NAME(tbl.schema_id) AS [Table_Schema], CAST(ISNULL(cik.index_column_id , 0) AS BIT) AS [InPrimaryKey], CAST(ISNULL(( SELECT TOP 1 1 FROM sys.foreign_key_columns AS colfk WHERE colfk.parent_column_id = clmns.column_id AND colfk.parent_object_id…