Append SQL Files into one File with PowerShell

Here’s a quick script to append all *.sql files into one file. This automagically appends a ‘GO’ statement between the files as a just in case. One issue (that I’m currently working on) is that this currently scripts the files by the name of the file. If the files have to be run in a certain order and has the order number in the file name (eg, 1_CreateTables.sql, 2_AddData.sql, etc…) it will mess the order up if there are more than 9 files. For example, if there were over 10 files, the order would be 1_CreateFiles.sql, 10_AddProcedures.sql. I’m still working on correcting this.

This will create a file called out.sql in the originating directory that contains all the contents of the files.

param(
	#[string]$path = $(throw "You must enter the path to the .sql files you want to append."),
	[string]$NewFileName = "Out.sql"
)

$path = "\\rbnnorfasp01p2\RCDDev1\Pipeline\Database\20111015\SQL Scripts\MSA Scripts\02_MSA_SProcScripts"
$outFile = "$path\$NewFileName"

cls
if((Test-Path $outFile) -eq $true) {Remove-Item -Path $outFile -Force}

$files = Get-ChildItem -LiteralPath $path -Include "*.sql" -Recurse | Sort-Object -Property Name

New-Item -ItemType file -Path $outFile -Force | Out-Null

foreach($file in $files)
{
	Write-Host "Appending file $file..." -ForegroundColor Gray
	$content = Get-Content -Path $file.FullName
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile "--		$File"
	Add-Content -Path $outFile "----------------------------------------------------------------------------------------------------------------------------------------------------------------"
	Add-Content -Path $outFile $content
	Add-Content -Path $outFile "GO`n"
}

Write-Host "Completed file $outFile" -ForegroundColor DarkGreen

Finding tables with no Primary Key Index in Powershell

Here’s a quick powershell script to list all tables with no primary key index in powershell. The servers.txt requires that you have the instance name, so if the server is a default instance, use SERVERNAME\DEFAULT as the instance name.

function FindTablesNoClustIdx([string]$ServerName)
{
	sl SQLSERVER:\SQL\$ServerName\DATABASES
	$databases = gci
	foreach($database in $databases)
	{
		$tables = $database.Tables
		foreach($table in $tables)
		{
			$hasPK = $false
			foreach($index in $table.Indexes)
			{
				if($index.IndexKeyType -eq [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey)
				{
					$hasPK = $true
				}
			}
			if($hasPK -eq $false)
			{
				Write-Host "Table $Table.Name in database $database.name on server $ServerName has no primary key index"
			}
		}
	}
}

$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

cls
$serverFile = "C:\Test\Servers.txt"
$servers = Get-Content $serverFile
foreach($server in $servers)
{
	FindTablesNoClustIdx $server
}

MSSearch Wait type + DROP DATABASE

Here’s a weird one. I tried to drop a database in our QA environment the other day, but the DROP DATABASE statement would never return. It would just sit there and spin its wheels. The database would indeed be dropped, but the statement never finishes executing. dbcc inputbuffer shows that just a drop database statement is running. Nothing exotic.

So, I query the sys.dm_os_waiting_tasks DMV to see what exactly this drop statement is waiting on, and low and behold:

Wait, what? MSSEARCH? New one on me. So I peruse over to event viewer and find this. I’ll tell you right now, this was a red herring. Nothing to do with the actual issue. If you see this in your event logs, it’s another issue entirely. I just don’t want you chasing down erroneous errors like I did. I ended up uninstalling windows search 4 due to this red herring.

The issue was actually an invalid full-text index. I had set up a process that takes a database and copies it to the same server nightly for testing dataloads so our QA dept. can do pre & post load comparisons. To get this database copied quickly (it’s rather large) I drop the current pre-load database, set the current load database offline and copy the files to a new location. I then bring the load database back online and re-attach the pre-load database. I had inadvertently not attached the fti files (although I did copy them correctly) when re-attaching the pre-load database.

I suspect the preload database was still pointing to the postload databases’ FTI files. That makes the most sense, as if it tried to drop the FTI files, the original database would still be using the files and not let them go. In the end, I ended up dropping the FTI in the source database before doing the copy, as it’s not being used anyway. I’m not the best FTI resource, I’ve used them very little.

USE [master]
GO
CREATE DATABASE [PreLoadDatabase] ON 
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.mdf' ),
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.ldf' ),
( FILENAME = N'E:\SqlData\PreLoadData\PreLoadDB.ndf' )
 FOR ATTACH
GO

Setting NOT FOR REPLICATION for all Foreign Key Constraints

Just a quick post on a powershell script I whipped up for setting the NOT FOR REPLICATION on all foreign key constraints in Sql Server.

Just a word of warning though, this will set your constraints to be not trusted. If you don’t know what that means, see here.

Unfortunately (AFAIK), the $key.NotForReplication cannot be set and altered for the foreign key object. It just errors out every time you call the Alter() method on the ForeignKey object. I tried just setting it and calling the Alter() method on the table instead, but it had no effect. Instead, I just script it out to the host and run the scripts manually. Interstingly enough, when you script out the ForeignKey object, the NotForReplication option is indeed included after you set it to $true. Whatever.

If one were so enterprising, one could create a function to run the output of the Script() methods directly into the database, but I am not that one.

Usual warning apply, run at your own risk.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptingOptions.ScriptDrops = $true

cls

$InstanceName = "ServerName\InstanceName"  #if not a named instance, ensure you put ServerName\DEFAULT as the instance name
$DBName = "DatabaseName"

sl "SQLSERVER:SQL\$InstanceName\DATABASES\$DBName\TABLES"

$tables = gci 

foreach($table in $tables)
{
	foreach($key in $table.ForeignKeys)
	{
		$key.NotForReplication = $true
		Write-Host $key.script($scriptingOptions);
		Write-Host $key.Script();
		#$key.Alter();
	}
}
#	$table.Alter();
#	write-host $table.Parent.Name
#	$table.Parent.Alter();