Sunday, December 20, 2015

Command Line SQL Server Interpreter


Update
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 


Commands:

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

  1. It only runs SELECT statements.
  2. 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.
  3. There is no way to export the data.
  4. 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.) 

To use the script 


  1. Copy/Download from the gist below.
  2. Set the $sqlServer   and $database variables at the top of script.
  3. If you are going to use SQL Server authentication set the $user and $pass variables
  4. If you are going to use SQL Server authentication comment out the Trusted Authentication  on line 100 and un-comment line 101.
  5. Run with powershell and have fun.



--- ---




No comments:

Post a Comment