Problem

As a developer I often have a settings table which is only editable by database tools (e.g. sql or text editor) and often only on the backend server. It would be nice to have a solution to allow easy editing while not being on the backend.

Solution

A small xcopy:able web site or application.
For database data it could have a connection string to the server and for text files it could have a path.

It should be able to figure out some meta data by itself but then, with local settings, override them to a more user friendly format.
For instance, if settings is a CSV table, the local settings could have definitions for separator characters, column headers and column data types.

This suggested solution is not for production but would solve the problem during development and even for production time backend manipulation in the right, careful hands. The same hands that otherwise would write sql queries to do the same task.

I am a developer and I most often use Sqlserver. The most common tool for working with Sqlserver is Sqlserver management studio and it does it’s job; which happens to be everything. Why not create a (relational) database tool targeted towards developers?

Things I normally don’t do

Now, as a developer I normally touch security at the beginning or end of a project. I usual handle just one of a couple of databases (database in sqlserver lingua, I believe it is called schema in Oracle) at a time. Space allocation is only used when doing backup and restore to create new or roll back a database. I have, unfortunately, never touched anything like sharding, replication, availability or other beardy stuff.

Things I normally do

I write queries, lots of queries. Depending on project I write stored procedures and triggers. I have also dealt with having DLLs inside Sqlserver. I do a lot of comparing schema and data with other databases.

What I want

I have a need for an IDE that leaves out all stuff I don’t or seldom use; whenever I need to dabble with security I can fire up Sqlserver management studio.

• awesome intelligence/typeahead/whateveryoucallit in the query editor. Management studios intellisense has become better but I still lack functionality I get with Sqlprompt by Redgate.  There are semi graphical tools for aiding query writing but I haven’t taken a serious stab at using them; I should.
• relations should be visible. When I work with a table I’d like to have indices, triggers and stored procedures in close vicinity, not through an “object explorer” tool window and a deep drill down tree. Say you are writing “select * from User” or selecting the User table in a list somewhere. Why not have a window, toolbar or menu automatically populate with depending tables, triggers and stored procedures?
• drill down possibility. Way to often I write a query and another and another just to find out where data comes from or vice verse. Without spending any time thinking and designing I am visualizing that when a row, say of a User, is found, one can with a click or key stroke get all Roles related to said User through the UserRoles table. Another example is User-> Order-> OrderItem-> Currency-> Country-> Company-> User.
• copy/paste update of table data. I also would like conversion of such copy or paste to a query to run on another instance.

What I want stuff that is really nice-to-have

• global search. I sometimes find a guid or a part of a string that has fled its sanctuary and need to find where in the database it originates. Somewhere I have a stored procedure for this but I’d like to have it built in to the tool and with more intelligence like searching for guids in proper fields and asking before doing a free text search in a million rows table.
• fast backup and restore. To take a temporary backup before doing a big or dangerous change. Then to restore said backup with ease. The Management studio dialogue for this is big, hard to use and error prone. By and by I need to create a copy of a database, say for branching a project, and that can too be made to be done with ease.
• no install. I manipulate data in production and don’t want to have to install anything; an xcopy should be enough.

As a Devop we often hack the database.  I see the need for a simpler tool than Toad/MSSQLEnterprise or even Queryexpress.  One that does only very simple things but easy and quick and safe.

Updating schemas aside I have presently found use for a simple record editor.  The simplest solution would be a hard code primary key and one field to update.

Sketch for a simple record editor

Specify the connection string, the primary key and the field in the config file.