Transfer Tables to a Different Schema via Powershell

Quick script to script out the transfer of tables belonging to on schema to the dbo schema.  This does no checking to see if the object exists in the dbo schema already, nor does it actually transfer the object.  It simply generates the script to do so.  I’m working on a more thorough version that will do all this, but I had a need for a quick-one off today.  This uses SQLPSX.

Import-Module SqlServer

Get-SqlServer -sqlserver 'ServerName' | %{
	Get-SqlDatabase -sqlserver $_ -dbname 'DatabaseName' | %{
		Get-SqlTable -Database $_ | where{$_.Schema -eq 'OrigSchema'} |%{
			"ALTER SCHEMA dbo TRANSFER [OrigSchema].[$($_.Name)]"
		}
	}
}

Get Table/Index Filegroup Listing using Powershell

Quick script to get a listing of tables\indexes and what filegroup said object belongs to via powershell.  This returns an array of PSObject.  The tables\indexes can be filtered via the ObjectType in the psobject array.  As always, this uses SQLPSX.

Import-Module SqlServer

$outputs = @();

Get-SqlDatabase -dbname 'database' -sqlserver 'server' | %{
	$db = $_
	Get-SqlTable -Database $_ | %{
		
		$output = New-Object -TypeName PSObject -Property @{
			ObjectName = $_.Name
			FileGroupName = $_.FileGroup
			ObjectType = "Table"
		}
		$outputs += $output
		
		$_.Indexes | %{
			$output = New-Object -TypeName PSObject -Property @{
				ObjectName = $_.Name
				FileGroupName = $_.FileGroup
				ObjectType = "Index"
			}
			$outputs += $output
		}
	}
	Get-SqlView -Database $_ | %{
		$vw = $_
		if($_.HasIndex)
		{
			$_.Indexes | %{
				$output = New-Object -TypeName PSObject -Property @{
					ObjectName = $_.Name
					FileGroupName = $_.FileGroup
					ObjectType = "Index"
				}
				$outputs += $output
			}
			
		}
	}
}

$outputs 

Get Login info using Get-SqlConnection

Quick post for someone on twitter on how to get login information using sqlpsxs’ Get-SqlConnection.  It appears that all you need is a sql login with public server role in order to list the logins on the server.  The only role that this login was in was the public role on the server.  Nothing else.

In order to get the Get-SqlLogins’ –sqlserver I passed in the ServerInstance from the Get-SqlConnection.  You could probably pipe this as Get-SqlLogin –sqlserver $(Get-SqlConnection –…) as well.

Import-Module SqlServer

$slqcn = Get-SqlConnection -sqlserver "YourServerName" -username "LoginName" -password "P@$$w0r9_123"
Get-SqlLogin -sqlserver $slqcn.ServerInstance | SELECT Name, LoginType, Language, IsSystemObject, CreateDate, DefaultDatabase | Format-Table -AutoSize

Get Database File Percentage Free Space via Powershell

Quick post to get the space left in a database file expressed as a percentage, in case you limit the size of your database files in sql server.  I’m not a fan of limiting database file sizes, expressly because most monitoring tools don’t check it (most dbas’ don’t check it either).  Most places I’ve been leave this unlimited and check the drive space (which most monitoring tools do check for).  There’s an argument to be made for each side, but I reside firmly on the unlimited side of the fence.  This happened to me twice yesterday, which only strengthens my argument.  This uses SQLPSX.

param(
	[string]$serverInstance = 'YourServerInstanceName'
)

Import-Module SqlServer

$outputs = @();

Get-SqlDatabase -sqlserver $serverInstance | %{
	$db = $_
	Get-SqlDataFile -database $_ | %{
		$output = New-Object -TypeName PSObject -Property @{
			DatabaseName = $db.Name
			FileLogicalName = $_.Name
			FileLocation = $_.FileName
			FileGrowthKB = $_.Growth
			MaxSizeKB = $_.MaxSize
			PercentFreeSpace = [Math]::Truncate(($_.AvailableSpace / $_.Size) * 100)
		}
		$outputs += $output	
	}
}
$outputs | SELECT DatabaseName, FileLogicalName, FileLocation, PercentFreeSpace, FileGrowthKB, MaxSizeKB

Script to Find Indexed Views

This script was precipitated by the bug about using indexed views and the MERGE statement.  I don’t use the MERGE much, as it makes my brain hurt, but they are used quite a bit at the company I work for.  Hence, I thought I would whip up a quick script to see if we were even using any indexed views.  As always, this script uses SQLPSX.

Import-Module SqlServer
$outputs = @();

get-sqldatabase -sqlserver 'YourServerName' | %{
	Get-SqlView -Database $_ | where{$_.HasIndex} | %{
		$output = New-Object -TypeName PSObject -Property @{
			DatabaseName = $_.Parent.Name
			ViewName = $_.Name
		}
		$outputs += $output
	}
}
$outputs | Format-Table -AutoSize

Getting Index Fragmentation via Powershell

Here’s a quick script to retrieve index fragmentation via powershell.  Two actually.  This uses sqlpsx for both.  Do not run these during the day on your production box (you shouldn’t run them at all unless you test them on a test box first to gauge impact).  I like the first one because you can strip off the –dbname parameter if you like and collect all the indexes from all your databases.  This uses LIMITED option (::fast), but it’s still pretty heavy-duty.  Use at your own risk.

First, here is the powershell-object oriented variant:

Import-Module SqlServer

$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'dbName' | %{
	
	$dbName = $_.Name
	
	Get-SqlTable -Database $_ | where{$_.IsSystemObject -ne $true} | %{
		$tableName = $_.Name
		
		foreach($index in $_.Indexes)
		{
			$index.EnumFragmentation([Microsoft.SqlServer.Management.Smo.FragmentationOption]::Fast)  | %{
				$output = New-Object -TypeName PSObject -Property @{
					DatabaseName = $dbName
					TableName = $tableName
					IndexName = $index.Name
					IndexType = $_.IndexType            
					AvgFragmentationInPercent = $_.AverageFragmentation    
					SizeMB = [Math]::Truncate(($_.pages*8)/1024)
					FillFactor = $index.FillFactor
				}
				$outputs += $output				
			}

		}
	}

}
$outputs | SELECT DatabaseName, TableName, IndexName, IndexType, AvgFragmentationInPercent, SizeMB, FillFactor | Format-Table -AutoSize

Nice and clean.  Unfortunately, if you run this while doing a trace, you’ll see that it’s doing much more work than necessary, but it’s very concise and easy to see/debug.

First, it executes this:

exec sp_executesql N'SELECT
i.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'TableName',@_msparam_3=N'SchemaName'

Then this:

exec sp_executesql N'
declare @database_id int
select @database_id = db_id()
			


SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [MinimumRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, ''LIMITED'') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[Index_Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IndexName',@_msparam_3=N'TableName',@_msparam_4=N'SchemaName'

Essentially, one hit to the database per table, and one per index on said table. 

You could use this powershell to do one hit to the database to get all the tables, then only suffer one hit per index:

cls
Import-Module SqlServer

$outputs = @();
$srv = 'ServerName'
Get-SqlDatabase -sqlserver $srv -dbname 'DatabaseName' | %{

	$dbName = $_.Name

	$idxQuery = "
	SELECT 
		DB_ID() AS DBID,
		i.object_id, 
		OBJECT_NAME(i.object_id) AS TableName,
		i.name, 
		i.index_id, 
		SUM(dps.used_page_count) * 8 
	FROM 
	sys.dm_db_partition_stats  dps 
	INNER JOIN sys.indexes i ON dps.[object_id] = i.[object_id] AND dps.index_id = i.index_id 
	GROUP BY i.name, i.index_id, i.object_id
	"
	$indexes = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxQuery

	foreach($row in $indexes)
	{
		$objectID = $row.object_id
		$indexID = $row.index_id
		$databaseID = $row.DBID
		
		$idxFragQuery = "
			SELECT 
				i.name, 
				i.type, 
				dmf.page_count*8 AS 'size', 
				ROUND(dmf.avg_fragmentation_in_percent,0) AS avg_fragmentation_in_percent, 
				i.fill_factor 
			FROM 
			sys.dm_db_index_physical_stats($databaseID, $objectID, $indexID, null, 'limited') dmf 
			INNER JOIN sys.indexes i ON ((i.object_id = dmf.object_id) AND (i.index_id = dmf.index_id)) 
			WHERE 
			(dmf.alloc_unit_type_desc = 'IN_ROW_DATA')
		"
		$idxFragRows = Get-SqlData -sqlserver $srv -dbname $dbName -qry $idxFragQuery
		$output = New-Object -TypeName PSObject -Property @{
			DatabaseName = $dbName
			TableName = $idxFragRows.TableName
			IndexType = $idxFragRows.type
			Size = $idxFragRows.size
			AvgFragmentationInPercent = $idxFragRows.avg_fragmentation_in_percent
			FillFactor = $idxFragRows.fill_factor
		}
		$outputs += $output
		
	}
}

$outputs | Format-Table -AutoSize

But, it’s not nearly as clean.  I wouldn’t run either of these on a production database during the day, but my preference would be to suffer the performance for the cleaner code, unless you have a gigantic number of tables in your database.  YMMV.  Use at your own risk.

Get a list of Objects owned by a Schema in Sql Server via Powershell

Quick script to get a list of objects owned by a schema in sql server.  This uses sqlpsx.  This doesn’t return the objects themselves, just a list of them.

Import-Module SqlServer
$outputs = @();
$srvName = 'ServerName'
$dbName = 'DatabaseName'
$schemaName = 'SchemaName'

#do not change, used for string parsing below
$startIndex = "@Name='"
$endIndex = "' and @Schema"

$srv = Get-SqlServer -sqlserver $srvName
$db = Get-SqlDatabase -sqlserver $srv -dbname $dbName
$schema = $db.schemas | where{$_.Name -eq $schemaName}
foreach($ownedObj in $schema.EnumOwnedObjects().GetEnumerator())
{
	$arObjs = $ownedObj.Value.Split("/")
	$newName = $arObjs[2].Substring($arObjs[2].IndexOf($startIndex)+ $startIndex.Length)
	
	$output = New-Object -TypeName PSObject -Property @{
		ObjectName = $newName.substring(0, $newName.indexof("'"));
		ObjectType = $ownedObj.Type
	}
	$outputs += $output

}

$outputs | Format-Table -AutoSize

Get VLF Counts for all Databases using Powershell

Quick script (as always) to get the vlf counts for all your databases via powershell.  Bit of a misnomer though, it just executes the sql for each database.  AFAIK, there is no mechanism in SMO to retrieve the vlf count directly.  This uses SQLPSX

param(
	[string]$serverName = "ServerName"
)
Import-Module SqlServer

$outputs = @();
$qry = @"
DBCC LOGINFO
"@

$server = Get-SqlServer -sqlserver $serverName
$dbs = Get-SqlDatabase -sqlserver $server | where{$_.IsSystemObject -ne $true}
foreach($db in $dbs)
{
	$res = Get-SqlData -dbname $db.name -sqlserver $server -qry $qry
	$output = New-Object -TypeName PSObject -Property @{
		DatabaseName = $db.Name
		VLFCount = $res.Count
	}
	$outputs += $output
}
$outputs | Format-Table -AutoSize

Powershell Script to drop a User from all Databases

Quick script to drop a user from all databases.  This does not drop the schema if said user owns one.  It will error if the user owns a schema.  This also uses SQLPSX.  Use at your own risk.

Import-Module SqlServer
$serverName = 'ServerName'
$loginToRemove = 'LoginOrUserName'

$dbs = Get-SqlDatabase -sqlserver $serverName | where{$_.IsSystemObject -eq $false}
foreach($db in $dbs)
{
	foreach($user in $db.Users | where{$_.Name -eq $loginToRemove})
	{
		$user.Drop();
	}
}