THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Browse and Script

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

(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 .

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

If I want to look at some stored procedure code, I can do that just as easily.

cd ../../../StoredProcedures
$spel = get-item HumanResources.uspUpdateEmployeeLogin

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.


Published Wednesday, September 16, 2009 12:11 PM by AllenMWhite
Filed under: ,


No Comments
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement