Tag: database

SQL If Exists Then Drop

This is one topic that I continually find myself refering to my notes and bookmarks on and I’ve finally decided to add this piece of very good reference information to my own blog.

Tables

Option 1:

IF OBJECT_ID('enterTableNameHere', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Option 2:

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterTableNameHere]')
         AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Views

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'enterSchemaNameHere' AND TABLE_NAME = 'enterViewNameHere'
)
BEGIN
    DROP VIEW [dbo].[enterViewNameHere]
END
GO

Stored Procedures

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterStoredProcedureNameHere]')
        AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
    DROP PROCEDURE [dbo].[enterStoredProcedureNameHere]
END
GO

User-Defined Functions

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE	ROUTINE_NAME = 'enterFunctionNameHere'
    AND ROUTINE_SCHEMA = 'dbo'
    AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
    DROP FUNCTION [dbo].[enterFunctionNameHere]
END
GO
Advertisements

SP 2010 Site Collection PowerShell Script

The following script is what we’ve been using to create site collections with their own database in SharePoint 2010.

Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue

## Configure the script
$databaseName = "SP_Content_Database_Name"
$webApplicationUrl = "http://localhost"
$siteCollectionUrl = "http://localhost/sites/siteName"
$siteCollectionName = "Site Collection Name"
$siteCollectionOwner1 = "DOMAIN\user1"
$siteCollectionOwner2 = "DOMAIN\user2"
$siteCollectionTemplate = "STS#1"

## Create the database
New-SPContentDatabase -Name $databaseName -WebApplication $webApplicationUrl

## Create the site collection
New-SPSite -URL $siteCollectionUrl -OwnerAlias $siteCollectionOwner1 -SecondaryOwnerAlias $siteCollectionOwner2 -ContentDatabase $databaseName -Name $siteCollectionName -Template $siteCollectionTemplate

Download the above code at: https://gist.github.com/4488619

If you need a list of site templates available on the machine you can run:

Get-SPWebTemplate | Sort-Object "Title"

SQL Server Foreign Keys and Indexes

This is another one of those cases that just beats it into your head to stop making assumptions.

My first delve into RDBMS’s was MySQL. When you create a Foreign Key in MySQL, it will create an Index for you. Unfortunately, I assumed that SQL Server would do the same. Then about a year ago, I was researching ways to speed up some of the queries in a customer’s application when I came across a thread on StackOverflow. What did I find? I was making the wrong assumption and you have to create both the Foreign Key and Index in SQL Server.

T-SQL String Padding Functions

Some time ago I found myself needing to format the data within a dataset. I thought about writing the code for it but then realized that a SQL implementation would probably be more efficient. I wrote two functions fPadLeft and fPadRight to allow a varchar(MAX) to be padded with any character (in my case it was zeros).

Here’s the fPadRight function:

CREATE FUNCTION fPadRight 
(
    @OrigString VARCHAR(MAX) = NULL,
    @PadLength INT = 0,
    @PadChar CHAR(1) = ''
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result VARCHAR(MAX); 
    DECLARE @OrigLength INT;

    SET @OrigLength = LEN(@OrigString);
    
    IF (@OrigLength >= @PadLength)
    BEGIN
        SET @Result = @OrigString
    END
    ELSE
    BEGIN
        SET @Result =  @OrigString + REPLICATE(@PadChar, @PadLength - @OrigLength);
    END

    RETURN @Result
END
GO

The entire scripts, including if exists logic, are included in the following links:

Pad Right: https://gist.github.com/4488425

Pad Left: https://gist.github.com/4488465