Saturday, April 21, 2012

I create a project on Google Code to gather all scripts, tables, utilities, documents, ... that I used and could be useful to other BI & DWH people.

You can download the very first release here:


It contains the SQL DDL scripts as well as the data in CSV format to create 4 dimensions for a Data Warehouse or Data Mart:

  • D_Country
  • D_Currency
  • D_Language
  • D_MinuteInDay

It's a simple zip file with 4 folders, one for each table. Just go to the desired subfolder and use the .sql and/or the .txt file


Enjoy, and comments are welcome !

Eric

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