Month: February 2013

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.

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.

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.

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.

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.

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.

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'

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.

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.