1. 只復(fù)制一個(gè)表結(jié)構(gòu)，不復(fù)制數(shù)據(jù)

????? ??? ?select?top?0?*?into?[t1]?from?[t2]
?????? ??? 或者? select * into?a from b where 1=2;

?????2.獲取數(shù)據(jù)庫中某個(gè)對(duì)象的創(chuàng)建腳本

????????if?exists(select?1?from?sysobjects?where?id=object_id('fgetscript')?and?objectproperty(id,'IsInlineFunction')=0)
?drop?function?fgetscript
go

create?function?fgetscript(
?@servername?varchar(50)?????--服務(wù)器名
?,@userid?varchar(50)='sa'????--用戶名,如果為nt驗(yàn)證方式,則為空
?,@password?varchar(50)=''????--密碼
?,@databasename?varchar(50)????--數(shù)據(jù)庫名稱
?,@objectname?varchar(250)????--對(duì)象名

)?returns?varchar(8000)
as
begin
?declare?@re?varchar(8000)????????--返回腳本
?declare?@srvid?int,@dbsid?int???????--定義服務(wù)器、數(shù)據(jù)庫集id
?declare?@dbid?int,@tbid?int????????--數(shù)據(jù)庫、表id
?declare?@err?int,@src?varchar(255),?@desc?varchar(255)?--錯(cuò)誤處理變量

--創(chuàng)建sqldmo對(duì)象
?exec?@err=sp_oacreate?'sqldmo.sqlserver',@srvid?output
?if?@err<>0?goto?lberr

--連接服務(wù)器
?if?isnull(@userid,'')=''?--如果是?Nt驗(yàn)證方式
?begin
??exec?@err=sp_oasetproperty?@srvid,'loginsecure',1
??if?@err<>0?goto?lberr

??exec?@err=sp_oamethod?@srvid,'connect',null,@servername
?end
?else
??exec?@err=sp_oamethod?@srvid,'connect',null,@servername,@userid,@password

?if?@err<>0?goto?lberr

--獲取數(shù)據(jù)庫集
?exec?@err=sp_oagetproperty?@srvid,'databases',@dbsid?output
?if?@err<>0?goto?lberr

--獲取要取得腳本的數(shù)據(jù)庫id
?exec?@err=sp_oamethod?@dbsid,'item',@dbid?output,@databasename
?if?@err<>0?goto?lberr

--獲取要取得腳本的對(duì)象id
?exec?@err=sp_oamethod?@dbid,'getobjectbyname',@tbid?output,@objectname
?if?@err<>0?goto?lberr

--取得腳本
?exec?@err=sp_oamethod?@tbid,'script',@re?output
?if?@err<>0?goto?lberr

?--print?@re
?return(@re)

lberr:
?exec?sp_oageterrorinfo?NULL,?@src?out,?@desc?out?
?declare?@errb?varbinary(4)
?set?@errb=cast(@err?as?varbinary(4))
?exec?master..xp_varbintohexstr?@errb,@re?out
?set?@re='錯(cuò)誤號(hào):?'+@re
???+char(13)+'錯(cuò)誤源:?'+@src
???+char(13)+'錯(cuò)誤描述:?'+@desc
?return(@re)
end
go

先執(zhí)行上述腳本

print?dbo.fgetscript('服務(wù)器名','用戶名','密碼','數(shù)據(jù)庫名','表名或其它對(duì)象名')
然后按上語句執(zhí)行即可。。


3.? 獲取所有數(shù)據(jù)庫里所有對(duì)象的腳本

??declare?@name?varchar(250)
declare?#aa?cursor?for
?select?name?from?sysobjects?where?xtype?not?in('S','PK','D','X','L')
open?#aa
fetch?next?from?#aa?into?@name
while?@@fetch_status=0
begin
?print?dbo.fgetscript('onlytiancai','sa','sa','database',@name)
?fetch?next?from?#aa?into?@name
end
close?#aa
deallocate?#aa


4.怎樣獲取一個(gè)表中所有的字段信息

Create?view?fielddesc????
as
select?o.name?as?table_name,c.name?as?field_name,t.name?as?type,c.length?as?

length,c.isnullable?as?isnullable,convert(varchar(30),p.value)?as?desp?
from?syscolumns?c??
join?systypes?t?on?c.xtype?=?t.xusertype
join?sysobjects?o?on?o.id=c.id?
left?join????sysproperties?p?on?p.smallid=c.colid?and?p.id=o.id????
where?o.xtype='U'
先執(zhí)行上述SQL語句

Select?*?from?fielddesc?where?table_name?=?'你的表名'

然后按上述的方法操作即可。。



5。當(dāng)前數(shù)據(jù)庫所有表的字段信息

SELECT?
?(case?when?a.colorder=1?then?d.name?else?''?end)?N'表名',
?a.colorder?N'字段序號(hào)',
?a.name?N'字段名',
?(case?when?COLUMNPROPERTY(?a.id,a.name,'IsIdentity')=1?then?'√'else?''?end)?N'標(biāo)識(shí)',
?(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)?N'主鍵',
?b.name?N'類型',
?a.length?N'占用字節(jié)數(shù)',
?COLUMNPROPERTY(a.id,a.name,'PRECISION')?as?N'長度',
?isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)?as?N'小數(shù)位數(shù)',
?(case?when?a.isnullable=1?then?'√'else?''?end)?N'允許空',
?isnull(e.text,'')?N'默認(rèn)值',
?isnull(g.[value],'')?AS?N'字段說明'
--into?##tx

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?object_name(a.id),a.colorder