1.一个简单的函数,去掉NEWID()中的横线,生成32位的UUID
USE [incubator]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fun_getid32](@newid varchar(36))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @id VARCHAR(32);
select @id=SUBSTRING(@newid,1,8)+SUBSTRING(@newid,10,4)+SUBSTRING(@newid,15,4)+ SUBSTRING(@newid,20,4)+SUBSTRING(@newid,25,12)
RETURN @id
END
--调用
select dbo.fun_getid32(cast(NEWID()as varchar(36)))
2.SqlServer2008,插入注释
--表注释
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'SYS_ACTION', default, default))
BEGIN
exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'SYS_ACTION'
END
exec sys.sp_addextendedproperty 'MS_Description', '权限表', 'schema', 'dbo', 'table', 'SYS_ACTION'
go
--字段注释
if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'SYS_ACTION', 'column', 'id'))
BEGIN
exec sys.sp_dropextendedproperty 'MS_Description', 'schema', 'dbo', 'table', 'SYS_ACTION', 'column', 'id'
END
exec sys.sp_addextendedproperty 'MS_Description', '权限ID', 'schema', 'dbo', 'table', 'SYS_ACTION', 'column', 'id'
go
3.JDBC
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=database
jdbc.username=sa
jdbc.password=sa
#SqlServer
jdbc.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc:jtds:sqlserver://localhost:1433/incubator;tds=8.0;lastupdatecount=true
jdbc.username=用户名
jdbc.password=密码
4.修改表后不能保存的问题
在sql server 2008的菜单栏“工具”下选择“选项”,在弹出对话框的左侧列表中有一个“设计器”,选择后,取消右侧“阻止保存要求重新创建表的更改”复选框的勾选,点击确认。
5.查询所有表、字段、类型、注释等
--数据库中所有表
select * from sys.tables t
--数据库中所有列
select * from sys.columns
--数据库中所有数据类型
select * from sys.types
--数据库中所有注释
select * from sys.extended_properties where major_id='2121058592'
--查询所有表的表名和注释
select
t.name,
case when p.value is null then ''
else p.value
end as value
from sys.tables t
left join sys.extended_properties p
on(t.object_id=p.major_id and p.minor_id=0)
--查询某张表中所有列名、数据类型、长度、是否为空和注释
select
c.name as columnName,
v.name as typeName,
c.max_length as length,
c.is_nullable as isNull,
p.value as comment
from sys.tables t,
sys.columns c,
sys.types v,
sys.extended_properties p
where t.object_id=c.object_id
and c.system_type_id=v.system_type_id
and c.column_id=p.minor_id
and t.object_id=p.major_id
and t.name='sys_user'
order by c.column_id
--构造数据
select
'Map<String,Object> '+c.name+'Map=new HashMap<String,Object>();'+
c.name+'Map.put("columnName","'+c.name+'");'+
c.name+'Map.put("typeName","'+v.name+'");'+
c.name+'Map.put("length","'+cast(c.max_length as varchar(10))+'");'+
c.name+'Map.put("isNull","'+cast(c.is_nullable as varchar(1))+'");'+
c.name+'Map.put("comment","'+cast(p.value as varchar(100))+'");'+
'list.add('+c.name+'Map);' as f
from sys.tables t,
sys.columns c,
sys.types v,
sys.extended_properties p
where t.object_id=c.object_id
and c.system_type_id=v.system_type_id
and c.column_id=p.minor_id
and t.object_id=p.major_id
and t.name='sys_user'
order by c.column_id