SQL Powershell

I’m getting a little tired of always clicking around in SSMS. Here goes a shot at powershell. I’ve done a few tutorials on powershell, but don’t use it enough to remember. The only thing I remember is Get-Child-Items and it’s alias GCI. That’s a start. Follow along with me.

User asks if a database was backed up last night. Of course it was, but let’s just make sure.

Right click on the databases node of the server I’m interested about in ssms and ‘Start PowerShell’

I end up at the databases path and try to copy the text out of the window with a mouse swipe to paste it in here. No dice, so I pop into properties and enable quick edit mode – I prefer it on in the CMD window. A highlight and a right-click will copy text out of the window.

PS SQLSERVER:\SQL\SERVER1\DEV08\Databases>

You can see the Server, instance name and then the databases node.

I enter my one command: GCI

A long list of databases fill the screen, but not the backup information I’m looking for. I know there is a powershell cmdlet (command-let a powershell command object) which will tell me more about the children of the database node – the databases. Fortunately, everyone on the web likes to talk about it. It’s the Get-Member cmdlet, which will tell you the methods and properties of whatever object you feed or ‘pipe’ it. Let’s pipe those database objects to the Get-Member alias.

GCI | GM

Whoah. That’s alot of output. I could use a little help sorting through that. Maybe I can pipe that list to another cmdlet and search for a string. There is probably a cmdlet called Find.

GCI | GM | FIND ‘backup’
FIND: Parameter format not correct

So how do I know what a cmdlet is expecting?

find /?

That gives me some help info, but changing the single quotes to double quotes doesn’t seem to do the trick.

Get-Help find

There’s a listing of somewhat unrelated commands. What did I just do there? What happens if I don’t give it a parameter.

Get-Help

TOPIC
Get-Help

SHORT DESCRIPTION
Displays help about Windows PowerShell cmdlets and concepts.

LONG DESCRIPTION

SYNTAX
get-help { | }
help { | }
-?

I expected to see a help page a bit more like this when I typed in Get-Help find, but instead I saw a listing. Find is being interpreted as a TopicName. So let’s type Get-Help find again – maybe there’s a more appropriate cmdlet I can use to find my search string in output of Get-Member. Select-String stands out to me – Finds text in strings and files.

GCI| GM | Select-String “Backup”

System.Void DropBackupHistory()
System.Data.DataTable EnumBackupSetFiles(int backupSetID), System.Data.DataTable EnumBackupSetFiles()
System.Data.DataTable EnumBackupSets()
System.Boolean IsDbBackupOperator {get;}
System.DateTime LastBackupDate {get;}
System.DateTime LastDifferentialBackupDate {get;}
System.DateTime LastLogBackupDate {get;}

There it is – LastBackupDate – and even more.

So I’ll accomplish my task at hand.

GCI | SELECT NAME, LASTBACKUPDATE, LASTLOGBACKUPDATE

But I’m only interested in a couple databases which start with “Risk”.

GCI | where { $_.name -like “Risk*”} | SELECT NAME, LASTBACKUPDATE, LASTLOGBACKUPDATE

The $_ represents the object being sent from the previous cmdlet. So you can filter on any property you found in the get-members listing.

Mission accomplished! That gives me what I need and I can feel smug about not clicking around in SSMS. True, it took me five minutes to learn how to do this and would take me a little longer than clicking on the two databases which were returned, but learning this will let me complete other more complex tasks.

Oh yeah, one other thing. Out-Host -Page will let you page through the sometimes lengthy manual pages. OH -p also works.

See all roles on your server

This cursor inserts all of the roles and members from each database into your admin database.


USE [SQL_DBA_Sandbox]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Roles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Roles](
[DBName] [varchar](max) NULL,
[Role] [varchar](max) NULL,
[Member] [varchar](max) NULL
) ON [PRIMARY]
END
ELSE
DELETE FROM SQL_DBA_Sandbox.dbo.Roles;
GO

DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL =
'INSERT INTO SQL_DBA_Sandbox.dbo.Roles
( DBNAME, ROLE, MEMBER )
SELECT ''' + @DB_NAME + ''',
r.name AS ''role'',
mem.name AS ''member''
FROM [' + @DB_Name + '].sys.database_role_members rm
INNER JOIN [' + @DB_Name + '].sys.database_principals r ON r.principal_id = rm.role_principal_id
INNER JOIN [' + @DB_Name + '].sys.database_principals mem ON mem.principal_id = rm.member_principal_id';
PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT DBName,[Role] FROM SQL_DBA_Sandbox.dbo.Roles
WHERE Role = 'analyst'

SELECT * FROM SQL_DBA_Sandbox.dbo.roles
WHERE Member = 'my_user'

See all the schemas on your server

Oh no! A useful cursor?


DECLARE @DB_NAME sysname;
SET @DB_NAME = NULL;

CREATE TABLE #RESULTS
(TABLE_CATALOG sysname,
TABLE_SCHEMA sysname)

DECLARE @SQL VARCHAR(2000);

DECLARE Cursor_DB CURSOR FAST_FORWARD READ_ONLY FOR

SELECT name FROM sys.databases
WHERE database_id > 4

OPEN Cursor_DB

FETCH NEXT FROM Cursor_DB INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'USE ['+ @DB_NAME + '];
INSERT INTO #RESULTS
( TABLE_CATALOG, TABLE_SCHEMA )
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT DISTINCT TABLE_CATALOG,TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
UNION ALL
SELECT DISTINCT ROUTINE_CATALOG,ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES;'

PRINT @SQL;

EXEC (@SQL);

FETCH NEXT FROM Cursor_DB INTO @DB_NAME

END

CLOSE Cursor_DB
DEALLOCATE Cursor_DB

SELECT DISTINCT TABLE_CATALOG, TABLE_SCHEMA FROM #RESULTS
WHERE TABLE_SCHEMA <> 'DBO'