Oct 03

How to Version Control Your Database Using Powershell

I just saw this post regarding why you should put your database into source control.

Unfortunately, it doesn’t get into much of the how, a question many project managers would love to point their developers to.

I’ll remedy that for you.

Since 2004, I have been using this practice. Ok, so I was first forced to, but it really helped pave the way for some really good developer habits (even if it meant doing something the company didn’t want to do… more on that later).

The first program I used was a windows application with a very simple screen configuration. It was the details needed to connect to a SQL Server (server, username, password, catalog), and an additional field for an XML configuration file.

This file essentially looked like this:

<configuration>
    <Schemas>
        <File>Schemas\File01.sql</File>
        <File>Schemas\File02.sql</File>
    </Schemas>
    <Packages>
        <File>Packages\File01.sql</File>
        <File>Packages\File02.sql</File>
    </Packages>
</configuration>

Again, this is a basic concept.

A “Schema” file was to only be run once. So things like table structures, indexes, constraints, etc, would go into this file. After the program ran, it would save the file path into a database table (that it created if it didn’t exist), and would check before running each time so as to not run more than once.

A “Package” file would be run every time. This covers Stored Procedures, Views, Functions, etc. At the beginning of the file, it contained a conditional drop statement if it already existed. This is probably the most controversial part of the program, since the stored procedures will lose it’s compiled run status each time you run a deployment. To that I say: “So?”.

I have taken the concept and written it as a C# windows app, updating it as time went on, and giving it to the companies I worked at. This has drastically changed how development goes. It makes finding differences in updates easier, makes searching for certain table calls faster, and puts you in the right direction for automating your deployments.

The updates I have given it were to rename the “Schemas” to “RunOnce”, “Packages” to “RunAlways”, and given it the concept of folders, in addition to files. This makes one of our multi-tenant projects with our Indian off-shore team development efforts go by much faster. It’s easier to update my software to accomodate their years of developer patterns, than to have them change their entire paradigm to fit my one small feature. I personally think it’s better this way, as the software now fits multiple programming styles, not just the one.

The new XML looks more like this:

<Database>
    <RunOnce>
        <File>Schemas\File01.sql</File>
        <File>Schemas\File02.sql</File>
        <!-- OR -->
        <Directory>Schemas</Directory>
    </RunOnce>
    <RunAlways>
        <File>Packages\File01.sql</File>
        <Directory>Packages\Views</Directory>
        <Directory>Packages\Functions</Directory>
        <Directory>Packages\StoredProcedures</Directory>
    </RunAlways>
</Database>

Anyway, I’ll share some of the pseudo code in how to get this to work properly, so you can get this going in your environment.

function SQL-Create-Connection($connectionstring)
{
# This will return a new System.Data.SqlClient.SqlConnection object based on the connection string
}

function SQL-Get-Connection($server, $catalog, $username, $password, $windows)
{
# This will return a new System.Data.SqlClient.SqlConnection object based on the individual parameters
}

function SQL-Check-Or-Create-DB-Version-Table($connection)
{
# This will create the following database table if it doesn't already exist
# db_version
#   filename VARCHAR(512) NOT NULL
}

function SQL-Execute-NonVersion-File($connection, $fullPath)
{
# This will open a file, split the contents based on "GO" statements, and execute the individual parts.
}

function SQL-Execute-Version-File($connection, $path, $fullPath)
{
    SQL-Check-Or-Create-DB-Version-Table -connection $connection
    
    # Now find out if the $path (relative to the XML configuration file) already exists
    # if ($Record -eq $null)
    # {
    #     try
    #     {
    #         SQL-Execute-NonVersion-File -connection $connection -fullPath $fullPath
    #         # Now insert the file into the version table
    #     }
    #     catch [Exception]
    #     {
    #         WRITE-WARNING "Problem processing file $fullPath"
    #         WRITE-WARNING $_.Exception.Message
    #         Throw
    #     }
    # }
}

function SQL-Execute-Table($connection, $commandText)
{
    # This will take the $commandText and create a table result for it
}

function SQL-Execute-NonQuery($connection, $commandText)
{
    # This will take the $commandText and just execute it
}

function SQL-Execute-Scalar($connection, $commandText)
{
    # This will take the $commandText and return the scalar after execution
}

function DB-Parse-Deployment-XML($XmlDocumentPath, $connection)
{
    # Open the XML Document, and get its directory to handle the relative paths
    $db_directory = $(Get-Item -path "$XmlDocumentPath").Directory.FullName
    $db_directory_regex = $("$($db_directory)\" -replace "\\", "\\")
    $db_xml = Get-Content $XmlDocumentPath

    try
    {
        # Loop through all of the RunOnce's...
        foreach ($runonce in $db_xml.Database.RunOnce)
        {
            foreach ($ro_file in $runonce.File)
            {
                $ro_file = $ro_file -replace "/", "\"
                $ro_file_full = "$db_directory\$ro_file"
                if (Test-Path $ro_file_full)
                {
                    $results = SQL-Execute-Version-File -connection $connection -path $ro_file -fullPath $ro_file_full
                    # Write-Host "RunOnce Relative File $ro_file"
                    # Write-Host "RunOnce Full File $ro_file_full"
                }
                else
                {
                    Write-Warning "RunOnce file was not found: $ro_file_full" 
                }
            }
            
            foreach ($ro_directory in $runonce.Directory)
            {
                $ro_directory = $ro_directory -replace "/", "\"
                $ro_directory_full = "$db_directory\$ro_directory"
                if (Test-Path $ro_directory_full)
                {
                    $scripts = Get-ChildItem "$ro_directory_full\*" -include *.sql -recurse
                    foreach ($script in $scripts)
                    {
                        $Relative_Script = $script -replace $db_directory_regex, ""
                        $results = SQL-Execute-Version-File -connection $connection -path $Relative_Script -fullPath $script
                    }
                }
                else
                {
                    Write-Warning "RunOnce Directory was not found: $ro_directory_full"
                }
            }
        }
        
        # Loop through all of the RunAlways'...
        foreach ($runalways in $db_xml.Database.RunAlways)
        {
            foreach ($ra_file in $runalways.File)
            {
                $ra_file = $ra_file -replace "/", "\"
                $ra_file_full = "$db_directory\$ra_file"
                if (Test-Path $ra_file_full)
                {
                    $results = SQL-Execute-NonVersion-File -connection $connection -fullPath $ra_file_full
                }
                else
                {
                    Write-Warning "RunAlways file was not found: $ra_file_full"
                }
            }
            
            foreach ($ro_directory in $runalways.Directory)
            {
                $ro_directory = $ro_directory -replace "/", "\"
                $ro_directory_full = "$db_directory\$ro_directory"
                if (Test-Path $ro_directory_full)
                {
                    $scripts = Get-ChildItem "$ro_directory_full\*" -include *.sql -recurse
                    foreach ($script in $scripts)
                    {
                        $results = SQL-Execute-NonVersion-File -connection $connection -fullPath $script
                    }
                }
                else
                {
                    Write-Warning "RunAlways directory was not found: $ro_directory_full"
                }
            }
        }
    }
    catch
    {
        Write-Error "Error in DB-Parse-Deployment-XML..."
    }
}

It took me a few hours to figure out what the contents of the first few methods were, but I think some of the fun as a developer is figuring out what those are. Since you have a very basic roadmap, it shouldn’t take too long to get this going all on your own.

Please feel free to let me know what you think in either the comments, or by shooting me an email from my contact page.

Thanks, and happy coding!

RJ

Apr 07

SQL Server String to Table User Defined Function

The following will create a user defined function (in the master table), and will allow a user to essentially split an entire string into individual rows by a character.

Why use it? I found it to be useful for creating a translation/obfuscation results.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2014-04-07
 Description: Splits a string into a table
  Parameters:
    @Input - String to split into a table
     Returns: Table
                Id INT
                Value NVARCHAR(1)
*/

IF EXISTS (SELECT * FROM sys.objects WHERE name='udf_TextToTable' AND schema_id=SCHEMA_ID('dbo') AND type='TF')
BEGIN
    DROP FUNCTION dbo.udf_TextToTable
END
GO

CREATE FUNCTION dbo.udf_TextToTable
(
    @Input NVARCHAR(MAX)
)
RETURNS 
@Return TABLE (Id INT, Value NVARCHAR(1))
AS
BEGIN
	-- Fill the table variable with the rows for your result set

    INSERT INTO @Return
        SELECT  T.ID, SUBSTRING(@Input, T.ID, 1) Value
        FROM    master.dbo.Tally T
        WHERE   T.ID BETWEEN 1 AND LEN(@Input)

    RETURN
END
GO

SELECT * FROM dbo.udf_TextToTable('Welcome to Denver Code Monkey!')
Posted in SQL
Feb 13

Drop All SQL Server Objects

The following script will drop all of the SQL Server objects.

PLEASE. USE. CAUTION.

DECLARE @n CHAR(1)
SET @n = CHAR(10)

DECLARE @stmt NVARCHAR(MAX)

PRINT 'Dropping Stored Procedures'
-- PROCEDURES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP PROCEDURE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.procedures

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Check Constraints'
-- CHECK CONSTRAINTS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.check_constraints

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Functions'
-- FUNCTIONS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP FUNCTION [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.objects
WHERE   TYPE IN ( 'FN', 'IF', 'TF' )

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Views'
-- VIEWS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP VIEW [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.views

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Foreign Keys'
-- FOREIGN KEYS
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'ALTER TABLE [' + schema_name(schema_id) + '].[' + object_name( parent_object_id ) + '] DROP CONSTRAINT [' + name + ']'
FROM    sys.foreign_keys

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Tables'
-- TABLES
SELECT  @stmt = isnull( @stmt + @n, '' ) +
        'DROP TABLE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.tables

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping User Defined Types'
-- USER DEFINED TYPES
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP TYPE [' + schema_name(schema_id) + '].[' + name + ']'
FROM    sys.types
WHERE   is_user_defined = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL

PRINT 'Dropping Schemas'
-- SCHEMAS
SELECT  @stmt = ISNULL( @stmt + @n, '' ) +
        'DROP SCHEMA [' + name + ']'
FROM    sys.schemas
WHERE   name <> N'dbo'
  AND   principal_id = 1

EXEC sp_executesql @stmt
SELECT @stmt = NULL
Posted in SQL
Dec 26

Backup Database Stored Procedure

The following script will handle Full backups, Differential backups, and transaction log backups.

/*   ____  _____ _____ 
    |    \|     |     |
    |  |  |   --| | | |
    |____/|_____|_|_|_|
      Author: RJ Hatch
 Create date: 2013-12-26
 Description: Backup Databases
  Parameters:
    @DatabaseName   - Name of the database
    @BackupType     - F=full, D=differential, L=log
    @BackupLocation - backup directory location
*/ 
CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
    @DatabaseName NVARCHAR(100),
    @BackupType CHAR(1),
    @BackupLocation NVARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON; 
           
    -- Declare variables
    DECLARE @BackupName NVARCHAR(100),
            @BackupFile NVARCHAR(100),
            @SqlCommand NVARCHAR(1000),
            @Date NVARCHAR(8),
            @Time NVARCHAR(6)

    -- Provide the backup a name for storing in the media
    SELECT  @Date = REPLACE(CONVERT(VARCHAR, GETDATE(), 102), '.', ''), --YYYYMMDD
            @Time = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), --HHMMSS
            @BackupFile = @backupLocation + REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') +
                CASE
                    WHEN @BackupType='F' THEN '_FULL_' + @Date + '_' + @Time
                    WHEN @BackupType='D' THEN '_DIFF_' + @Date + '_' + @Time
                    ELSE '_LOG_' + @Date + '_' + @Time
                END + 
                CASE
                    WHEN @BackupType='F' THEN '.BAK'
                    WHEN @BackupType='D' THEN '.BAK'
                    ELSE '.TRN'
                END, --[c:\directory\][Database]_[LongType]_[Date][_Time].[Ext]
            @BackupName = REPLACE(REPLACE(@DatabaseName, '[', ''), ']', '') + ' ' +
                CASE
                    WHEN @BackupType='F' THEN 'full'
                    WHEN @BackupType='D' THEN 'differential'
                    ELSE 'log'
                END + ' backup for ' + @Date + '_' + @Time, -- [Database] [BackupType] backup for [Date]_[Time]
            @SQLCommand = 'BACKUP ' +
                CASE
                    WHEN @BackupType='F' THEN 'DATABASE'
                    WHEN @BackupType='D' THEN 'DATABASE'
                    ELSE 'LOG'
                END + ' ' +
                @DatabaseName + ' TO DISK = ''' + @BackupFile + ''' WITH ' +
                CASE
                    WHEN @BackupType='D' THEN 'DIFFERENTIAL, '
                    ELSE ''
                END + 'INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'

    -- Execute the generated SQL command
    EXEC(@sqlCommand)
END

Run it as the following

--Full Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'F', 'c:\temp\'

--Differential Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'D', 'c:\temp\'

--Transaction Backup
exec master.dbo.sp_BackupDatabase 'AdventureWorks', 'L', 'c:\temp\'
Posted in SQL
Dec 19

SQL Server Tally Table

The following Tally Table will help in future posts.

The Tally table is simply a table with an ID column, that will help in reducing most loops; a personal preference of mine.

CREATE TABLE dbo.Tally (
    ID INT NOT NULL
)
GO
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally
    PRIMARY KEY CLUSTERED ( ID )
GO

--2048 * 8 = 16384 Results
INSERT INTO dbo.Tally
    SELECT  a.number * 2048 + b.number + 1 ID
      FROM  [master].dbo.spt_values a,
            [master].dbo.spt_values b
    WHERE   a.[type] = 'p'
      AND   b.[type] = 'p'
      AND   b.number < 2048
      AND   a.number < 8
    ORDER BY a.number, b.number
GO

If you want more results, you will need to mess with the “a.number < 8" line in the where clause. Each increase will result in an extra 2048 entries. I find that 16k+ suits my needs just fine.

Posted in SQL
Nov 25

Dropping SQL Server Cache

If you are anything like me, you want to know if your updates to your database have improved on a 0-60 basis or not.

Unfortunately, if you run the same query back-to-back, then you will often run into SQL Server caching the query and optimizing your results.

The following will clean out the cache for your testing and analysis purposes.

USE {DATABASE};
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
Posted in SQL
Oct 30

Drop All SQL Server Default Constraints on a Table

Recently I was working on updating some tables, and the SQL that was generated for the update included some code that looked like this:

ALTER TABLE [schema].[table]
    DROP CONSTRAINT [DF__Table__Column__a789cv78s7s]

Well, it dawned on me that the various environments we have *COULD* have different constraint names.

Now, these constraints will be deleted when the table is dropped, so an explicit drop is not necessarily required.

However, if you actually name your default constraints, rather than utilize the shorthand in the table definition, then you may run into an issue of duplicate default constraint names.

This handy script will drop all of the defaults on a particular table.

DECLARE @SchemaName NVARCHAR(256),
        @TableName NVARCHAR(256),
        @SQL NVARCHAR(MAX),
        @NewLine CHAR(1)

SELECT  @SchemaName = N'dbo',
        @TableName = N'TheNameOfTheTable',
        @NewLine = CHAR(10)

SELECT  @SQL = ISNULL(@SQL + @NewLine, '') + 
            'ALTER TABLE [' + S.name + '].[' + T.name + '] ' +
                'DROP CONSTRAINT [' + D.name + ']'
FROM    sys.tables T
    INNER JOIN sys.default_constraints D
        ON D.parent_object_id = T.object_id
    INNER JOIN sys.columns C
        ON C.object_id = T.object_id
            AND C.column_id = D.parent_column_id
    INNER JOIN sys.schemas S
        ON T.schema_id = S.schema_id
WHERE   S.name = @SchemaName
  AND   T.name = @TableName

PRINT @SQL
EXECUTE (@SQL)
Posted in SQL
Sep 26

SQL Bulk Insert

So I’m sure there are a ton of resources already out there regarding this, so this is more for me.

To use the BULK INSERT command, you’ll first need to create your data file.

ID|StateID|ZipCode|City|County|State|TimeZone|PopulationPercent|FIPS
1|33|00501|HOLTSVILLE|Suffolk|NY|EST|100|36103
2|33|00501|I R S SERVICE CENTER|Suffolk|NY|EST|100|36103
3|33|00544|HOLTSVILLE|Suffolk|NY|EST|100|36103
4|33|00544|IRS SERVICE CENTER|Suffolk|NY|EST|100|36103
5|58|00601|ADJUNTAS|Adjuntas|PR||100|43001
...

Then you’ll need to create the SQL statement.

BULK INSERT [Table]
FROM 'c:\Dir\File.txt'
WITH (
     DATAFILETYPE = 'char',
     FIELDTERMINATOR = '|',
     FIRE_TRIGGERS,
     MAXERRORS = 50,
     TABLOCK
     )

And there you go!

Much faster than a crap-ton of insert statements 🙂

Posted in SQL
Apr 25

How to Get Rid of Cursors for Inserting Bulk Data

This is pseudo code, but you should be able to get the idea from here…

Will develop more as requested…

DECLARE @t TABLE (Columns Def, repeat ...)

insert into @t
  select from flat_file

insert into parent
  select parent, fields from @t

insert into child
  select p.parentid, c.child, c.fields
  from	@t c
    inner join parent p
      on c.parentkey = p.parentkey
Posted in SQL
Apr 14

New Web-based SQL Query Tool

I had an idea at work a while ago that would allow an authorized user (based on the Intranet) to dynamically execute a stored procedure in a designated schema.

The need arose when I discovered that our network/system administrator was searching for saved queries, changing some variables, connecting to the production server, and then executing them.

I thought it would be nice to put these into their own schema that a page could find these, and prepare a nice and easy way for them to be executed.

Development is still in process, and I’m hoping to have version 1 released in the next few weeks.

If you or your company would like to stay up to date on this, or if you have any questions or input, please ship me an email in the form below.

What will come in these versions:

Free Paid Ultimate
Get a list of procedures based on a schema X X X
Display meta-data about procedure X X X
Display meta-data about parameters X X X
Data input – Textbox X X X
Data input – Checkboxes X X
Data input – Radio Buttons X X
Data input – Drop Down List X X
Populate input with Data (SQL) X X
Populate input with Data (String) X X
Populate cascading input with Data (SQL) X
Data Validation – More Than/Equal X X
Data Validation – Less Than/Equal X X
Data Validation – Required X X
Data Validation – Is Date X X
Data Validation – Is Numeric X X
Data Validation – Regular Expression X X
Visual way to manage Proc X

[contact-form-7 id=”204″ title=”SQL Tool”]