存儲過程：
set ansi_nulls on
go
set quoted_identifier on
-- =============================================
-- author: <carbe>
-- create date: <2014-09-19>
-- description: <生成資料庫字典>
create procedure [dbo].[createdatabasedictionarie]
as
begin
declare @tablename nvarchar(35),@htmls varchar(8000)
declare @字段名稱 varchar(200)
declare @類型 varchar(200)
declare @長度 varchar(200)
declare @數值精度 varchar(200)
declare @小數位數 varchar(200)
declare @預設值 varchar(200)
declare @允許為空 varchar(200)
declare @外鍵 varchar(200)
declare @主鍵 varchar(200)
declare @描述 varchar(200)
set nocount on;
declare tbls cursor
for
select distinct table_name
from information_schema.columns
order by table_name
open tbls
print '<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">'
print '<html xmlns="http://www.w3.org/1999/xhtml">'
print ' <head>'
print ' <title>kc管理系統-資料庫字典</title>'
print ' <style type="text/css">'
print ' .tablebox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#fbf5e3; border:1px solid #45360a}'
print ' .tablebox h3 {font-size:12pt; height:30px; line-height:30px; background:#45360a; padding:0px 0px 0px 15px; color:#fff; margin:0px; text-align:left }'
print ' .tablebox table {width:1000px; padding:0px }'
print ' .tablebox th {height:25px; border-top:1px solid #fff; border-left:1px solid #fff; background:#f7ebc8; border-right:1px solid #e0c889; border-bottom:1px solid #e0c889 }'
print ' .tablebox td {height:25px; padding-left:10px; border-top:1px solid #fff; border-left:1px solid #fff; border-right:1px solid #e0c889; border-bottom:1px solid #e0c889 }'
print ' </style>'
print ' </head>'
print ' <body>'
fetch next from tbls into @tablename
while @@fetch_status = 0
select @htmls = ' <h3>' + @tablename + ' : '+ cast(value as varchar(1000)) + '</h3>'
from sys.extended_properties as a
where a.major_id = object_id(@tablename)
and name = 'ms_description' and minor_id = 0
print ' <div class="tablebox">'
print @htmls
print ' <table cellspacing="0">'
print ' <tr>'
print ' <th>字段名稱</th>'
print ' <th>類型</th>'
print ' <th>長度</th>'
print ' <th>數值精度</th>'
print ' <th>小數位數</th>'
print ' <th>預設值</th>'
print ' <th>允許為空</th>'
print ' <th>外鍵</th>'
print ' <th>主鍵</th>'
print ' <th>描述</th>'
print ' </tr>'
declare trows cursor
select
' <td>' + cast(clmns.name as varchar(35)) + '</td>',
' <td>' + cast(udt.name as char(15)) + '</td>' ,
' <td>' + cast(cast(case when typ.name in (n'nchar', n'nvarchar') and clmns.max_length <> -1 then clmns.max_length/2 else clmns.max_length end as int) as varchar(20)) + '</td>',
' <td>' + cast(cast(clmns.precision as int) as varchar(20)) + '</td>',
' <td>' + cast(cast(clmns.scale as int) as varchar(20)) + '</td>',
' <td>' + isnull(cast(cnstr.definition as varchar(20)),'') + '</td>',
' <td>' + cast(clmns.is_nullable as varchar(20)) + '</td>' ,
' <td>' + cast(clmns.is_computed as varchar(20)) + '</td>' ,
' <td>' + cast(clmns.is_identity as varchar(20)) + '</td>' ,
' <td>' + isnull(cast(exprop.value as varchar(500)),'') + '</td>'
from sys.tables as tbl
inner join sys.all_columns as clmns on clmns.object_id=tbl.object_id
left outer join sys.indexes as idx on idx.object_id = clmns.object_id and 1 =idx.is_primary_key
left outer join sys.index_columns as idxcol on idxcol.index_id = idx.index_id and idxcol.column_id = clmns.column_id and idxcol.object_id = clmns.object_id and 0 = idxcol.is_included_column
left outer join sys.types as udt on udt.user_type_id = clmns.user_type_id
left outer join sys.types as typ on typ.user_type_id = clmns.system_type_id and typ.user_type_id = typ.system_type_id
left join sys.default_constraints as cnstr on cnstr.object_id=clmns.default_object_id
left outer join sys.extended_properties exprop on exprop.major_id = clmns.object_id and exprop.minor_id = clmns.column_id and exprop.name = 'ms_description'
where (tbl.name = @tablename and exprop.class = 1) --i don't wand to include comments on indexes
order by clmns.column_id asc
open trows
fetch next from trows into @字段名稱,@類型,@長度,@數值精度,@小數位數,@預設值,@允許為空,@外鍵,@主鍵,@描述
print @字段名稱
print @類型
print @長度
print @數值精度
print @小數位數
print @預設值
print @允許為空
print @外鍵
print @主鍵
print @描述
end
close trows
deallocate trows
print ' </table>'
print ' </div>'
print ' </body>'
print '</html>'
close tbls
deallocate tbls
當然這些通過pring出來的代碼使用傳統的方式是調用不到的，通過查找資料，終于在國外一個xxx網站找到了解決方案。
private static string message = "";
public static string executenonquery(string connextionstring, commandtype commandtype, string commandtext, bool outputmsg)
{
if (connextionstring == null || connextionstring.length == 0) throw new argumentnullexception("connectionstring");
// create & open a sqlconnection, and dispose of it after we are done
using (sqlconnection connection = new sqlconnection(connextionstring))
message = "";
connection.open();
connection.infomessage += delegate(object sender, sqlinfomessageeventargs e)
message += "\n" + e.message;
};
// call the overload that takes a connection in place of the connection string
if (connection == null) throw new argumentnullexception("connection");
// create a command and prepare it for execution
sqlcommand cmd = new sqlcommand(commandtext, connection); ;
cmd.commandtype = commandtype;
// finally, execute the command
int retval = cmd.executenonquery();
// detach the sqlparameters from the command object, so they can be used again
cmd.parameters.clear();
connection.close();
return message;
}
調用就不用寫了嘛。一切就這麼簡單，生成的是一份标準的htm代碼，可直接放到html裡面，當然也可以直接從資料庫讀取出來顯示。
最新内容請見作者的github頁：http://qaseven.github.io/
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)