I've turned this into a project on GitHub, See this post for more info
I frequently have to debug data issues and reports on a SQL server database. In a lot of cases I just need to look up a few fields from a record or do a quick foreign key lookup. A SQL query tool or SSMS is overkill for the job because it takes to long to load. SQLCMD requires a SSMS installation and I don't always have it installed where I'm working. So I wrote this lightweight powershell. script for these situations
Command Line SQL Server Interpreter
help -- Shows other valid commands.
SELECT ... -- Runs any valid select statement.
def -- Shows one column of the table vertically so you can see field name.
quit -- Disconnects from the database and closes the window.
The program ignores any other commands.
It has quite a few limitations though
- It only runs SELECT statements.
- It only displays as many columns as it can fit in the command line screen. (you can adjust the shortcut to make it wider, but its not going to handle a big row very well.
- There is no way to export the data.
- Multi line scripts look pretty ugly.
Example of rudimentary help command and a SELECT statement
Note that it cuts off the rest of the row on long rows (select * not recommended)
There is a def command which lists all the column names in the table (with the data from an arbitrary row.)
- Copy/Download from the gist below.
- Set the $sqlServer and $database variables at the top of script.
- If you are going to use SQL Server authentication set the $user and $pass variables
- If you are going to use SQL Server authentication comment out the Trusted Authentication on line 100 and un-comment line 101.
- Run with powershell and have fun.