One of the cool features of the SQL Server PowerShell snapins is the the ability to browse SQL Server as though it was a file system. While Management Studio has the nice graphical tree structure, sometimes I find it more productive to walk up and down the tree in a text environment. One thing I really like to see is the DDL script for the objects of interest because that script tells me a lot in a concise manner.
(Either run SQLPS.exe from SQL Server 2008, or use the script in Michiel Wories' blog post here to load the SQL Server PowerShell snapins.)
So, how do I see the script of the objects I'm looking at? It's easy, once you know.
Let's say we're browsing the AdventureWorks database and I want to look at the tables.
cd SQLServer:\SQL\SQLTBWS\INST01\Databases\AdventureWorks\Tables
ls
(Note that 'cd' is an alias for Set-Location and 'ls' is an alias for Get-ChildItem.)
These commands return the list of tables in AdventureWorks. If I change to the Person.Address "directory" I can easily script the table DDL like this:
cd Person.Address
$pa = get-item .
$pa.Script()
If I then change to the indexes directory and list the items there, I see four indexes. I can easily script one using these commands:
$ixp = get-item IX_Address_StateProvinceID
$ixp.Script()
If I want to look at some stored procedure code, I can do that just as easily.
cd ../../../StoredProcedures
$spel = get-item HumanResources.uspUpdateEmployeeLogin
$spel.Script()
Of course, you can also pipe these objects to the out-file cmdlet and save the script to text files if you'd like. The important thing is that you can browse your system easily and find useful information to help you understand your current application architecture.
Allen