Sunday, March 18, 2012

Free Dimension Table: D_MinuteInDay

If you need a dimension table for the Minute in a Day for your Data Warehouse or Data Mart, you can download the following zip file. It contains the DDL for SQL Server 2008 as well as the data in a txt file.
The data file is a semi-colon delimited file, with double-quotes as text-qualifier.



Feel free to use...

Eric

sp_DropView: A simple T-SQL stored proc to drop a view in any database


A simple T-SQL procedure to drop a view in any database of the current server. The goal is to have the view removed, was it there or not. Feel free to use...




CREATE PROCEDURE [dbo].[sp_DropView]
@Database nvarchar(255),
  @Owner nvarchar(255),
@View nvarchar(255)
AS
BEGIN
  DECLARE @SQLStmt varchar(8000);
SET NOCOUNT ON;
SET @SQLStmt = '
IF  EXISTS (
  SELECT * FROM ' + @Database + '.sys.views
  WHERE
  object_id = OBJECT_ID(N''' + @Database + '.' + @Owner + '.' + @View + ''')
  )
  EXEC ' + @Database + '..sp_executesql N''DROP VIEW ' + @Owner + '.' + @View + '''
  ';
  EXEC (@SQLStmt);
END
GO



Eric

sp_DropTable: A simple T-SQL stored proc to drop a table in any database

A simple T-SQL procedure to drop a table in any database of the current server. The goal is to have the table removed, was it there or not. Feel free to use...


CREATE PROCEDURE [dbo].[sp_DropTable]
   @Database nvarchar(255),
   @Owner nvarchar(255),
   @Table nvarchar(255)
AS
BEGIN
   DECLARE @SQLStmt varchar(8000);
   SET NOCOUNT ON; SET @SQLStmt = '
   IF EXISTS (
      SELECT * FROM ' + @Database + '.sys.objects
      WHERE
         object_id = OBJECT_ID(N''' + @Database + '.' + @Owner + '.' + @Table + ''')
      AND type in (N''U'')
      )
   DROP TABLE ' + @Database + '.' + @Owner + '.' + @Table;
   EXEC (@SQLStmt);
END
GO


Eric