Smartvehiclefinance - Download Download Smartvehiclefinance Download Smartvehiclefinance You Download search1 You 
searchs
ar
Smartvehiclefinance w Download w. You 2
r Smartvehiclefinance v Download hil Download fsearchn Download nsearche9999pp%B5%E7%D3%B0 You e Download r Smartvehiclefinance h Download searcho You nsesearchr
hsearch Smartvehiclefinance o You s Download awww%2Ebb252%2Ecomc Download Smartvehiclefinance You Download Download Download Download Smartvehiclefinance Smartvehiclefinance You searchsearch
You searchsearch Smartvehiclefinance Smartvehiclefinance www.345iii.con You search Smartvehiclefinance Smartvehiclefinance searchsearchsearch Download You searchawww.ai222.com
--清除临时表
if object_id('tempdb..#db1') is not null drop table #db1
if object_id('tempdb..#db2') is not null drop table #db2
if object_id('tempdb..#db2a') is not null drop table #db2a
if object_id('tempdb..#db3') is not null drop table #db3
--将数据库Northwind 字典信息（表名，字段名，字段类型名）插入临时表#db1
select a.name tname,
b.name cname,
c.name tpname,
COLUMNPROPERTY(b.id, b.name, 'PRECISION') tplength,
isnull(COLUMNPROPERTY(b.id, b.name, 'Scale'), 0) tpdec,
(case
when b.isnullable = 1 then
'null'
else
'not null'
end) tpisnull,
isnull(d.text, '') tpdefault
into #db1
from Northwind .. sysobjects a, Northwind .. syscolumns b, Northwind .. systypes c,Northwind..syscomments d
where a.id = b.id
and a.xtype = 'U'
and b.xusertype = c.xusertype
and b.cdefault *= d.id
--将数据库pubs 字典信息（表名，字段名，字段类型名）插入临时表#db2
select a.name tname,
b.name cname,
c.name tpname,
COLUMNPROPERTY(b.id, b.name, 'PRECISION') tplength,
isnull(COLUMNPROPERTY(b.id, b.name, 'Scale'), 0) tpdec,
(case
when b.isnullable = 1 then
'null'
else
'not null'
end) tpisnull,
isnull(d.text, '') tpdefault
into #db2
from pubs .. sysobjects a, pubs .. syscolumns b, pubs .. systypes c,pubs..syscomments d
where a.id = b.id
and a.xtype = 'U'
and b.xusertype = c.xusertype
and b.cdefault *= d.id
--检查两数据库中的表的数目是否相同
select count(*) tablecounts from (select distinct tname from #db1) T1
select count(*) tablecounts from (select distinct tname from #db2) T2
--数目不相同，则只找出#db2在#db1中，相同表的记录，并插入到临时表#db2a
select tname,cname,tpname,tplength,tpdec,tpisnull,tpdefault
into #db2a
from #db2 a
where EXISTS(select 1 from #db1 b where a.tname=b.tname )
--检查两数据库中的表的数目
select count(*) tablecounts from (select distinct tname from #db1) T1
select count(*) tablecounts from (select distinct tname from #db2a) T2
--将#db1中存在的表，与#db2中相同表进行比较，比较他们的字段是否相同
--查出的结果为在数据库1（#db1）中，需要增加的字段
select * into #db3 from #db2a a
where not EXISTS(select 1 from #db1 b where (b.tname+b.cname +b.tpname)=(a.tname+a.cname +a.tpname) )
--===============================================================
--创建游标
declare cs_db3 cursor for
select tname,cname,tpname,tplength,tpdec,tpisnull,tpdefault from #db3
open cs_db3
fetch cs_db3 into @dtemp1,@dtemp2,@dtemp3,@dtemp4,@dtemp5,@dtemp6,@dtemp7
while @@fetch_status=0
begin
select @sqlstr=' '
select @sqlstr=@sqlstr+' alter table '+@dtemp1+' add ' +@dtemp2 + ' ' + @dtemp3
exec(@sqlstr)
fetch cs_db3 into @dtemp1,@dtemp2,@dtemp3,@dtemp4,@dtemp5,@dtemp6,@dtemp7
end
close cs_db3
deallocate cs_db3
posted @ 2010-03-25 12:21 sangsmart 阅读(72) 评论(0) 编辑
--使用系统函数
SELECT HOST_ID() 主机ID,
HOST_NAME() 主机名,
DB_ID() 数据库ID,
DB_NAME() 数据库名,
USER_ID() 数据库用户ID,
USER_NAME() 数据库用户名,
SUSER_ID() 当前SQL_Server身份验证登录的用户ID,
SUSER_NAME() 当前SQL_Server身份验证登录的用户名
--用 DATALENGTH 函数来确定特定值中的字符总数
use Northwind
SELECT COL_LENGTH('Employees', 'LastName') AS Col_Length,
DATALENGTH(LastName) AS DataLength
FROM Employees
WHERE EmployeeID > 6
--用 OBJECT_ID 函数来确定对象ID,用OBJECT_NAME函数来确定对象名
USE Northwind
SELECT OBJECT_ID('Employees') 对象ID ,OBJECT_NAME(OBJECT_ID('Employees'))对象名
--==============================================================================
--取Northwind数据库Employees表结构信息
use Northwind
SELECT A.name AS '字段',
B.name AS '类型',
A.length AS '长度',
CASE
WHEN A.isnullable = 1 THEN
'NULL'
ELSE
'NOT NULL'
END AS '是否为空'
FROM sysobjects AS C LEFT JOIN syscolumns AS A ON C.id = A.id LEFT JOIN
systypes B ON
A.xtype = B.xtype WHERE
C.name = 'Employees'
--============================sqlsvr2000==========================================
--列出当前数据库所有表，字段名，主键，类型，长度，小数位数等信息
SELECT (case
when a.colorder = 1 then
d.name
else
''
end) 表名,
a.colorder 字段序号,
a.name 字段名,
(case
when COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then
'√'
else
''
end) 标识,
(case
when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id)
AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id)
AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id)
AND (name = a.name)))))))
AND (xtype = 'PK')) > 0 then
'√'
else
''
end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as 小数位数,
(case
when a.isnullable = 1 then
'√'
else
''
end) 允许空,
isnull(e.text, '') 默认值,
isnull(g. value, '') AS 字段说明
FROM syscolumns a left join systypes b on a.xtype = b.xusertype
inner join sysobjects d on a.id = d.id
and d.xtype = 'U'
and d.name <> 'dtproperties' left join
syscomments e on a.cdefault = e.id left join
sysproperties g on
a.id = g.id
AND a.colid = g.smallid
order by a.id, a.colorder
--============================sqlsvr2005==========================================
dYou Smartvehiclefinance De Download Smart Vehicle Finance sangsmart - 博客园i r Www.no345.com k Www.345fff.com Www.yojizz.com cYou Smartvehiclefinance De Download Smart Vehicle Finance sangsmart - 博客园y a Www.haoie20.com n 777%C6%E6%C3%D7%D3%B0%CA%D3 Www.sex8.con