简单的SQL Server在线查看和执行系统

      在大的公司里面，往往数据库和表都非常的多，一张表的字段也是会有几十个。如果这么多的表和字段没有说明注释的话，查看起来会一头雾水，新来的或者别的部门的同事看到表和字段会完全不知道是干嘛的，只能靠名称来猜。为了避免这种情况，在数据库的设计的时候就要去写说明。在之前传统的开发中一般会写个数据字段的文档，但是这显然不符合节奏快，需求多，老变更的敏捷公司。对于数据库数据来说，普通开发人员是不应该有线上数据库权限的，最多也只能有一个查看的权限，更多的操作（新增字段，修改字段）都应该交给专业的DB团队去操作。所以普通的大公司都会有自己开发的在线查看数据库字段和在线执行SQL语句的系统。

      近几日工作比较清闲，就这么重复的造了下轮子，自己按照原有的需求写了一个简单的系统。在线访问页面地址：http://www.pcstx.cn

　　先给大家看下界面：

这是查看字段的界面。可以在线修改表的描述和字段的说明。

这是在线执行SQL语句的界面。可以执行多条SQL语句。这边显示的比较简单。

web.config里面可以配置是读取连接字符串还是通过登陆输入。

      整个项目代码比较简单，就是查询系统表。页面用到ligerUI，数据库访问层用的是Dapper。在缓存上面，只是在左侧树上面做了缓存，对于大的项目表字段也应该使用缓存也能弄一个导出的功能。

连接字符串单独的写到了 App_Data\connectionStrings.config 里面。分为读写连接和只读连接，目的是为了在执行SQL语句的地方不能执行非查询操作。这边控制就靠数据库的权限了。

代码里面一些主要的SQL语句：

查询服务器中所有数据库名称

 string dbSQL = "select dbid as id,name,crdate as createDate,type=‘database‘,connName=‘"+connectionStringName+@"‘ from master..sysdatabases with(nolock) where dbid > 4 order by name;--用户库";          

查询数据库中所有的表名

 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) on 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);

查询表中所有字段的相关信息　　

  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=ISNULL(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.default_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                       -- 索引及主键信息
                                        (  SELECT IDXC.[object_id],IDXC.column_id,PrimaryKey=IDX.is_primary_key
                                            FROM sys.indexes IDX with(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 O.name=@tableName       -- 如果只查询指定表,加上此条件
                                    ORDER BY O.name,C.column_id ";

                rowSQL = string.Format(rowSQL, dbName);

修改字段说明

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]=PFD.major_id AND C.column_id=PFD.minor_id        
	                        left JOIN  [{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 ";  //判断是否存在，不存在新增，存在修改
                dbName = dbName.Replace(‘\‘‘, ‘ ‘);
                sql = string.Format(sql, dbName, TableName, rowName, Description);

实现代码也比较简单，在此提供我写的代码供大家吐槽。 

 点我下载

简单的SQL Server在线查看和执行系统

标签：