Simple SQL Server online viewing and execution system

In large companies, often the database and the table are very much, a table of the field will also have dozens of. If so many of the tables and fields are not annotated, it will be confused to see that the new or other department colleagues see that the tables and fields are completely unknown and can only be guessed by name. To avoid this, write a description when the database is designed. In the traditional development before the general will write a data field of the document, but this obviously does not meet the pace, demand, the old change of the agile company. For database data, ordinary developers should not be wired database permissions, at most can only have one view of the permissions, more operations (new fields, modify the field) should be given to the professional DB team to operate. So the average big company will have its own system to view database fields and execute SQL statements online.

In recent days, work relatively leisurely, so repeated the creation of the next wheel, in accordance with the original needs to write a simple system. Online access page address: http://www.pcstx.cn

Let's first look at the interface:

This is the interface for viewing fields. You can modify the description of the table and the description of the field online.

This is the interface for executing SQL statements online. You can execute more than one SQL statement. This is a relatively simple display.

The Web. config can be configured to read the connection string or enter it via login.

The whole project code is simple, that is, querying system tables. The page is used to Ligerui, and the database access layer is dapper. On the cache, just cache on the left tree, and for large project table fields, you should also use caching to get an export function.

The connection string is written separately into the app_data\connectionstrings.config. It is divided into read-write connections and read-only connections in order to not perform non-query operations where SQL statements are executed. The control on this side depends on the database permissions.

Some of the main SQL statements in the code:

Querying all database names on the server

String dbsql = "Select Dbid as id,name,crdate as createdate,type= ' database ', connname= '" [email protected] ' from master. sysdatabases with (NOLOCK) where dbid > 4 order by name;--User Library ";          

Querying all table names in the database

String tablesql = @ "Select t.object_id as id,t.name as name,t.create_date as CreateDate,                                    t.modify_date as Modifydate,s . Value as Note,databasename= ' {0} ', type= ' table ', Connname= ' {1} ' from                                    [{0}].sys.objects T with (NOLOCK)                                    left Join [{0}].sys.extended_properties s with (NOLOCK) in t.object_id=s.major_id and s.minor_id=0                                      where [type] = ' u ' and is _ms_shipped=0                                    order by name; ";                Dbname=dbname.replace (' \ ' ', ');                Tablesql = string. Format (Tablesql, dbname,connectionstringname);

Querying information about all fields in a table　　

  String rowsql = @ "Select Id=c.column_id,name=c.name,primarykey=isnull (IDX. Primarykey,n "), rowtype=t.name,lenght=c.max_length,isnull=c.is_nullable,defaultvalue=i                                    Snull (d.definition,n "), Note=isnull (pfd.[value],n"), type= ' Row ' From [{0}].sys.columns-C with (NOLOCK) INNER JOIN [{0}].sys.objects O With (NOLOCK) on c.[object_id]=o.[object_id] and o.type= ' U ' and o.is_ms_shipped=  0 INNER JOIN sys.types T with (NOLOCK) on                                            c.user_type_id=t.user_type_id left JOIN sys.default_constraints D with (NOLOCK) On c.[object_id]=d.parent_object_id and c.column_id=d.parent_column_id and C.defau LT_OBJECT_ID=D.[OBJECT_ID] LEFT JOIN sys.extended_properties PFD with (NOLOCK) on Pfd.class=1 and c.[object_id ]=pfd.major_id and c.column_id=pfd.minor_id left JOIN--Index and master Key Information (SELECT idxc.[ Object_id],idxc.column_id,primarykey=idx.is_primary_key from Sys.indexes IDX wi                                    Th (nolock) INNER JOIN sys.index_columns idxc with (NOLOCK) On IDX. [OBJECT_ID]=IDXC. [OBJECT_ID] and idx.index_id=idxc.index_id) IDX on C.[object_id]=idx. [OBJECT_ID]                                     and c.column_id=idx.column_id WHERE [email protected]--If you only query the specified table, add this condition                ORDER by o.name,c.column_id "; Rowsql = string. Format (Rowsql, dbName);

Modify the field description

String sql = @ "if exists (select * from [{0}].sys.extended_properties PFD with (NOLOCK) Left join [{0}].sys.columns C with (NOLOCK) on Pfd.class=1 and C.[OBJECT_ID]=PF                        D.MAJOR_ID and c.column_id=pfd.minor_id left joins [{0}].sys.objects O with (NOLOCK) On c.[object_id]=o.[object_id] and o.type= ' U ' and o.is_ms_shipped=0 where C.name= '                            {2} ' and O.name= ' {1} ') begin use [{0}]; EXEC sp_updateextendedproperty n ' ms_description ', ' {3} ', n ' user ', n ' dbo ', n ' table ', n ' {1} ', n ' column ', n ' {2}                        ' End else begin use [{0}]; EXECUTE sp_addextendedproperty n ' ms_description ', ' {3} ', n ' user ', n ' dbo ', n ' table ', n '      {1} ', n ' column ', n ' {2} '                   End ";                The judgment exists, there is no new, there is modification dbName = dbname.replace (' \ ', '); sql = string. Format (SQL, DbName, TableName, RowName, Description);

　　

Implementation code is also relatively simple, here I write the code for everyone to spit groove.

Click I download

Simple SQL Server online viewing and execution system