[转]SQL 2005中自动生成数据字典
create proc [dbo].[GenerateDataDictionary] as
begin
--获取数据表名
declare @tableid int
declare mycursor Cursor
for select object_id from sys.objects where type='U' and name<>'dtproperties'
--获取字段名称、标识、字段序号、占用字节数、小数位数、允许空等
open mycursor
fetch next from mycursor into @tableid
while(@@fetch_status=0)
begin
print '表名：'+object_name(@tableid)
select
col.colorder 字段序号,
col.name 字段名,
t.name 类型,
col.length 占用字节数,
COLUMNPROPERTY(col.id,col.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(col.id,col.name,'Scale'),0) as 小数位数,
(case when
(SELECT count(*)
from sysobjects
WHERE (name in
(SELECT name
from sysindexes
WHERE (id = col.id)
AND (indid in
(SELECT indid
from sysindexkeys
WHERE (id = col.id) AND (colid in
( SELECT colid
from syscolumns
WHERE (id = col.id)
AND (name = col.name)
相关文档：
Sql代码
--采用SQL语句实现sql2005和Excel 数据之间的数据导入导出，在网上找来一--下，实现方法是这样的：
--Excel---->SQL2005 导入：
select * into useinfo from O ......
package com.itcast.service.base;
import java.util.LinkedHashMap;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.transaction.annotation.Propagation;
import org.spri ......
using (DataClasses1DataContext a = new DataClasses1DataContext())
{
}
这样写比较方便
更新时,如果表没有主键,那不会更新,也不会报错,设了主键后,重开连接,重新拖一个到Context里面
如果表的映射类不是从context里面搜出来的,而是自己new的
用context.表类.Attach(外面的类,true/false)更新 ......
