---try
sysaltfiles 主資料庫儲存資料庫的檔案
syscharsets 主資料庫字符集與排序順序
sysconfigures 主資料庫配置選項
syscurconfigs 主資料庫當前配置選項
sysdatabases 主資料庫伺服器中的資料庫
syslanguages 主資料庫語言
syslogins 主資料庫登陸帳號資訊
sysoledbusers 主資料庫連結伺服器登陸資訊
sysprocesses 主資料庫程式
sysremotelogins 主資料庫遠端登入帳號
syscolumns 每個資料庫列
sysconstrains 每個資料庫限制
sysfilegroups 每個資料庫檔案組
sysfiles 每個資料庫檔案
sysforeignkeys 每個資料庫外部關鍵字
sysindexs 每個資料庫索引
sysmembers 每個資料庫角色成員
sysobjects 每個資料庫所有資料庫物件
syspermissions 每個資料庫許可權
systypes 每個資料庫使用者定義資料型別
sysusers 每個資料庫使用者
/**//*********************************************************************************
* FielName : backup.sql
* Function : 自動備份
* Author : Yahong
* Date : 2005-5-10 2005-5-19 2006-8-1 2007-09-18
* Version : 00 01 02 03
*
* Remark :
* 2006-08-01 增加差異備份和完全備份兩種情況,生成多個備份副本
* 2008-09-18 增加備份一個例項中的所有資料庫的情況,並在備份後清除日誌
*
*********************************************************************************/
use master
declare @DbName varchar(255),@dir varchar(256),@dir_db varchar(256),
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256),@copy nvarchar(100),
@today datetime,@weekday int
--建立網路連線
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'

exec xp_cmdshell 'net use K: \\193.254.40.118\backup backup /user:Web\backup '
exec xp_cmdshell 'net use I: \\172.16.8.48\databackup backup /user:QA-SERVER-TEST\backup'
--設定名字
set @today=getdate()
set @dynamic_name=convert(varchar(10),@today,120)
set @dir='K:\'+@dynamic_name
set @dir_db=@dir+'Database'
set @verb='mkdir '
--建立目錄
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
declare cur_database cursor forward_only read_only for
select name from sysdatabases
where dbid>4 --系統資料庫的dbid<=4
open cur_database
fetch next from cur_database
into @DbName

while @@fetch_status=0
begin
set @backup_name= @DbName+'_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name+'.bak'

--新增備份裝置
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name

set @weekday= datepart(dw,@today)
if (@weekday=6) --如果是週五,則進行完全備份
BACKUP DATABASE @DbName TO @backup_name
else --其他時候進行差異備份
BACKUP DATABASE @DbName TO @backup_name with differential
--清理日誌
backup log @DbName with no_log
--釋放裝置
exec sp_dropdevice @backup_name
--複製備份副本到其他地方
set @copy='copy '+@disk_name+' I:'
exec xp_cmdshell @copy

--備份下一個資料庫
fetch next from cur_database
into @DbName
end
close cur_database
deallocate cur_database
--刪除網路連線
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use I: /delete'
/**//******************************************************************************
*
* File Name : Restore.sql
* Function : 資料庫還原
* Author : Yahong
* Version : 00
* Date : 2007-09-18
* Remark :
*
*******************************************************************************/
use master
declare
@DbName varchar(255) --資料庫的名字
,@WholeFileName varchar(255) --完全備份的檔名
,@DifferentFileName varchar(255) --差異備份的檔名
,@MasterFileName varchar(255) --資料檔名,注意他們都是邏輯名稱
,@LogFileName varchar(255) --日誌檔名
,@TargetDir varchar(255) --還原後資料庫檔案所在的路徑，如果沒有指定該引數，
--則必須存在與原資料庫相同的路徑
declare @WholeDeviceName varchar(255)
,@DifferenctDeviceName varchar(255)
,@TargetMasterFileName varchar(255)
,@TargetLogFileName varchar(255)
--建立網路連結
exec xp_cmdshell 'net use K: \\172.16.8.48\200709 backup /User:qa-server-test\backup'
--在這裡設定需要備份的檔案等資訊
set @DbName='CCTQA' --需要還原的資料庫的名字，注意不要搞錯了，否則
--覆蓋了其他的資料庫，可別說我沒有提醒你
set @WholeFileName='CCTQA_2007-09-14.bak' --完全備份檔案

--以下4行如果沒有，不要指定,把他們註釋掉就行了
set @DifferentFileName='CCTQA_2007-09-17.bak' --最後一次差異備份檔案
set @MasterFileName='CCTQA_Data' --資料檔案
set @LogFileName='CCTQA_Log' --日誌檔案
set @TargetDir='D:\CCTQA\Databae' --目標路徑
--設定目標路徑
set @TargetMasterFileName=@TargetDir+'\'+@MasterFileName
set @TargetLogFileName=@TargetDir+'\'+@LogFileName
--新增還原裝置
set @WholeDeviceName=@DbName+'WholeDevice'
set @WholeFileName='K:\'+@WholeFileName
exec sp_addumpdevice 'disk',@WholeDeviceName,@WholeFileName
--開始備份
if(isnull(@DifferentFileName,'')<>'') --如果具有差異備份的還原
begin
--新增差異備份還原的裝置
set @DifferenctDeviceName=@DbName+'DifferenctDevice'
set @DifferentFileName='K:\'+@DifferentFileName
exec sp_addumpdevice 'disk',@DifferenctDeviceName,@DifferentFileName

--備份
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeDeviceName
with NORECOVERY
else --如果還原後的資料庫檔案的路徑與備份前的路徑不一致
restore database @DbName from @WholeDeviceName
with NORECOVERY,
move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
restore database @DbName from @DifferenctDeviceName
end
else
begin --只有完全備份的還原
if(isnull(@TargetDir,'')='')
restore database @DbName from @WholeFileName
else
restore database @DbName from @WholeFileName
with move @MasterFileName to @TargetMasterFileName,
move @LogFileName to @TargetLogFileName
end
--釋放備份裝置
exec sp_dropdevice @WholeDeviceName
if(isnull(@DifferentFileName,'')<>'')
exec sp_dropdevice @DifferenctDeviceName
--刪除網路連結
exec xp_cmdshell 'net use K: /delete'
/**//************************************************************************
*
* File Name : ShrinkLog.sql
* Function : 收縮資料庫的日誌檔案
* Author ：Yahong
* Version : 00
* Date : 2007-09-16
* Remark :
*
*************************************************************************
--
--第一步：設定需要收縮的資料庫，找到需要收縮資料檔案
--
use cctqa
select Size/128 Size,Name from sysfiles
/**//*
declare @LogName varchar(255),@TargetSize int
--
--
--第二步：設定需要收縮的日誌檔案的邏輯名字和收縮後的大小
--千萬不要搞錯了，選錯了檔案，有可能會丟失資料，那時候
--哭都哭不回來了。
--
--
set @LogName='CCTQA_Log'
set @TargetSize=1
declare @str varchar(300), @DatabaseName varchar(255)
set @DatabaseName=db_name()
if(not exists(select * from sysfiles where name=@LogName))
begin
set @str='沒有找到日誌檔案'+@LogName
raiserror(@str,0,1)
end else
begin
declare @curSize int,@maxTime int
set @maxTime=10
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收縮之前的日誌檔案的大小是：'+cast(@curSize as varchar(10))+'MB'
while (@curSize>@TargetSize) and (@maxTime>0)
begin
backup log @DatabaseName with no_log
DBCC SHRINKFILE(@LogName,@TargetSize)

set @curSize=(select size from sysfiles where name=@LogName)/128
set @maxTime=@maxTime-1
end
set @curSize=(select size from sysfiles where name=@LogName)/128
print '收縮之後的日誌檔案的大小是：'+cast(@curSize as varchar(10))+'MB'
end
*/
==================第二種==========================================
-- ConfigureDistribution.sql
-- Scripting replication configuration for server CA\SQLA.
-- Installing the server CA\SQLA as a Distributor.
use master
GO
exec sp_adddistributor @distributor = N'CA\SQLA', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution'
, @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, @data_file_size = 4
, @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO
use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty
('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\Ca\ReplData', 'user',
dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', '\\Ca\ReplData', 'user',
dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'CA\SQLA'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'\\Ca\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO
-- CreatePublication.sql
use [TestDB]
exec sp_replicationdboption @dbname = N'TestDB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [TestDB]
exec sp_addpublication @publication = N'TestDB'
, @description = N'Transactional publication of database ''TestDB'' from Publisher ''CA\SQLA''.'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'true'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @ftp_login = N'anonymous'
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @autogen_sync_procs = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TestDB'
, @frequency_type = 1
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 0
, @publisher_login = N'sa'
, @publisher_password = N''
use [TestDB]
exec sp_addarticle @publication = N'TestDB'
, @article = N'Family'
, @source_owner = N'dbo'
, @source_object = N'Family'
, @type = N'logbased'
, @description = N''
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'Family'
, @destination_owner = N'dbo'
, @status = 0
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboFamily'
, @del_cmd = N'CALL sp_MSdel_dboFamily'
, @upd_cmd = N'SCALL sp_MSupd_dboFamily'
, @filter_clause = N'[ID] < 100'
-- Adding the article filter
exec sp_articlefilter @publication = N'TestDB'
, @article = N'Family'
, @filter_name = N'FLTR_Family_1__57'
, @filter_clause = N'[ID] < 100'
, @force_invalidate_snapshot = 1
, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'TestDB'
, @article = N'Family'
, @view_name = N'SYNC_Family_1__57'
, @filter_clause = N'[ID] < 100'
, @force_invalidate_snapshot = 1
, @force_reinit_subscription = 1
GO
-- NewSubscription.sql
-- BEGIN: Script to be run at Publisher 'CA\SQLA'
use [TestDB]
exec sp_addsubscription @publication = N'TestDB'
, @subscriber = N'ABCDE\SQL2005'
, @destination_db = N'TestDB'
, @subscription_type = N'Push'
, @sync_type = N'automatic'
, @article = N'all'
, @update_mode = N'read only'
, @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'TestDB'
, @subscriber = N'ABCDE\SQL2005'
, @subscriber_db = N'TestDB'
, @job_login = null
, @job_password = null
, @subscriber_security_mode = 0
, @subscriber_login = N'sa'
, @subscriber_password = null
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20080910
, @active_end_date = 99991231
, @enabled_for_syncmgr = N'False'
, @dts_package_location = N'Distributor'
GO
-- END: Script to be run at Publisher 'CA\SQLA'
--
--sql server 2005
-- 1. 表結構資訊查詢
-- ========================================================================
-- 表結構資訊查詢
-- 鄒建 2005.08(引用請保留此資訊)
-- ========================================================================
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 欄位說明對應的描述名稱(一個欄位可以新增多個不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表說明對應的描述名稱(一個表可以新增多個不同name的描述)
LEFT JOIN -- 索引及主鍵資訊
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 對於一個列包含多個索引的情況,只顯示第1個索引資訊
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查詢的表' -- 如果只查詢指定表,加上此條件
ORDER BY O.name,C.column_id

-- 2. 索引及主鍵資訊
-- ========================================================================
-- 索引及主鍵資訊
-- 鄒建 2005.08(引用請保留此資訊)
-- ========================================================================
SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,'Index'),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
Fill_factor=IDX.fill_factor,
Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
-- INNER JOIN -- 對於一個列包含多個索引的情況,只顯示第1個索引資訊
-- (
-- SELECT [object_id], Column_id, index_id=MIN(index_id)
-- FROM sys.index_columns
-- GROUP BY [object_id], Column_id
-- ) IDXCUQ
-- ON IDXC.[object_id]=IDXCUQ.[object_id]
-- AND IDXC.Column_id=IDXCUQ.Column_id
/**************************
系統資料庫中查詢表的所有欄位以及描述（2008，2000區分）以及查詢表的外來鍵
***************************/
----2008下
-------方法一----表的擴充套件屬性01------
SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 't_bill_in', 'column', default)

SELECT
CAST(value AS nvarchar(200)) as tableDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'T_Bill_Cedula_Detail', default, default);

-----方法二----表的擴充套件屬性描述-----
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表說明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號 = a.colorder,
欄位名 = a.name,
標識 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
型別 = b.name,
佔用位元組數 = a.length,
長度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空 = case when a.isnullable=1 then '√'else '' end,
預設值 = isnull(e.text,''),
欄位說明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=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
dbo.dtproperties g
on
a.id=g.id and a.colid=g.objectid
left join
dbo.dtproperties f
on
d.id=f.id and f.objectid=0
where
d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by
a.id,a.colorder

----方法03----表欄位的描述（簡易）
Select
col.[name] as '欄位名',
col.[length]as '長度' ,
type.[name] as '型別' ,
pro.value as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join dbo.dtproperties as pro on col.id = pro.id and col.colid = pro.objectid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

---2000下------------------------
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表說明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
欄位序號 = a.colorder,
欄位名 = a.name,
標識 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主鍵 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
型別 = b.name,
佔用位元組數 = a.length,
長度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數位數 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空 = case when a.isnullable=1 then '√'else '' end,
預設值 = isnull(e.text,''),
欄位說明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=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
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where
d.name='要查詢的表' --如果只查詢指定表,加上此條件
order by
a.id,a.colorder
=-----------方法02---表的描述2000下----
Select
col.[name] as '欄位名',
col.[length]as '長度' ,
type.[name] as '型別' ,
pro.value as '描述'
From syscolumns as col
Left Join systypes as type on col.xtype = type.xtype
Left Join sysProperties as pro on col.id = pro.id and col.colid = pro.smallid
where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

-----------------查詢一個表的所有外來鍵
SELECT 主鍵列ID=b.rkey
,主鍵列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,外來鍵表ID=b.fkeyid
,外來鍵表名稱=object_name(b.fkeyid)
,外來鍵列ID=b.fkey
,外來鍵列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,級聯更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,級聯刪除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.rkeyid)='titles'

SELECT *
FROM information_schema.columns
WHERE TABLE_CATALOG='資料庫名'
AND TABLE_NAME = '表名'
AND COLUMN_NAME='列名'

select *
from syscolumns
where id=object_id('tableName') and name='fieldName'

------------2005以及2008中，查詢表的欄位---------------------
DECLARE @tableName nvarchar(100)
SET @tableName ='tab'
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 sys.extended_properties g ON a.id=g.major_id AND a.colid = g.major_id WHERE d.name=@tableName
ORDER BY a.id,a.colorder
SELECT
CAST(value AS nvarchar(200)) as tableDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 't_bank', default, default);
declare @tablename varchar(100)
set @tablename=''
SELECT
objname
,CAST(value AS nvarchar(200)) as fieldDescription
FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName , 'column', default) AS E
=====================================================================================================================
SQL2005自動備份和自動刪除三天前的備份
1 declare @data_3ago nvarchar(50)
2 declare @cmd varchar(50)
3
4 set @data_3ago ='e:\data\'+convert(varchar(10),getdate()-3,112)
5 set @cmd = 'del '+ @data_3ago
6 exec master..xp_cmdshell @cmd
7 go
8
9 declare @data nvarchar(50)
10 set @data='e:\data\'+convert(varchar(10),getdate(),112)
11 BACKUP DATABASE job TO DISK = @data
12 with init
===================================================
.獲取表的基本欄位屬性
--獲取SqlServer中表結構
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id('你的表名')
執行效果
2.如果還想要獲取欄位的描述資訊則
--獲取SqlServer中表結構主鍵，及描述
declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id

執行效果

3.單獨查詢表的遞增欄位

--單獨查詢表遞增欄位
select [name] from syscolumns where
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1
執行效果

4.獲取表的主外來鍵
--獲取表主外來鍵約束
exec sp_helpconstraint '你的表名' ;
執行效果
==============================================

/*檢視資料庫離線時間*/
/*author lcw 2008-10-21*/
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
go
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
select a.name,a.database_id,a.create_date,b.physical_name into #a
from sys.databases a left join sys.master_files b on
a.database_id=b.database_id where has_dbaccess(a.name)<>1 and b.type=1

create table #b(info varchar(500))
declare @string varchar(max)
set @string=''
select @string=@string+'insert into #b exec xp_cmdshell''dir '+ physical_name +''''+char(13)+char(10) from #a
execute(@string)

select a.name,substring(b.info,0,20) as 離線時間,a.database_id,a.create_date,a.physical_name
from #a a left join #b b on
REVERSE(substring(REVERSE(physical_name),0,charindex('\',REVERSE(physical_name))))
=REVERSE(substring(REVERSE(info),0,charindex(' ',REVERSE(info))))

drop table #a,#b
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
go
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
go

=======================================

--檢視作業執行情況
select category = jc.name,
category_id = jc.category_id,
job_name = j.name,
job_enabled = j.enabled,
last_run_time = cast(js.last_run_date as varchar(10)) + '-' + cast(js.last_run_time as varchar(10)),
last_run_duration = js.last_run_duration,
last_run_status = js.last_run_outcome,
last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
job_created = j.date_created,
job_modified = j.date_modified
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobservers js
on j.job_id = js.job_id
inner join msdb.dbo.syscategories jc
on j.category_id = jc.category_id
where j.enabled = 1
and js.last_run_outcome in (0,1,3,5) -- 0:Fail 1:Succ 3:Cancel 5:First run
and jc.category_id not between 10 and 20 -- repl
=================================================

/*========================================================
過程描述：實現SQL2005 資料庫檔案移動到指定目錄路徑
建立者： LCW
建立日期：2008-07-29
===========================================================*/
USE master
GO
DECLARE
@DBName sysname,
@DestPath varchar(256),
@DestPath1 varchar(256)
DECLARE @DB table(
name sysname,
physical_name sysname)
BEGIN TRY
SELECT
@DBName = '', --input database name
@DestPath = 'K:\data\', --input destination DATA path
@DestPath1 = 'L:\LOG\' --input destination LOG path
--kill database processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR
SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName
OPEN curProcess
FETCH NEXT FROM curProcess INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('KILL ' + @SPID)
FETCH NEXT FROM curProcess
END
CLOSE curProcess
DEALLOCATE curProcess
--query physical name
INSERT @DB(
name,
physical_name)
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
WHERE A.type <=1
--set offline
EXEC('ALTER DATABASE [' + @DBName + '] SET OFFLINE')
--move to dest path
DECLARE
@login_name sysname,
@physical_name sysname,
@temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
name,
physical_name
FROM @DB
OPEN curMove
FETCH NEXT FROM curMove INTO @login_name,@physical_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_name = RIGHT(@physical_name,CHARINDEX('\',REVERSE(@physical_name)) - 1)
IF RIGHT(RTRIM(@TEMP_NAME),3)='LDF'
BEGIN
EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath1 + '"''')
EXEC('ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @login_name
+ '], FILENAME = ''' + @DestPath1 + @temp_name + ''')')
END
ELSE
BEGIN
EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''')
EXEC('ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @login_name
+ '], FILENAME = ''' + @DestPath + @temp_name + ''')')
END
FETCH NEXT FROM curMove INTO @login_name,@physical_name
END
CLOSE curMove
DEALLOCATE curMove
--set online
EXEC('ALTER DATABASE [' + @DBName + '] SET ONLINE')
--show result
SELECT
A.name,
A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
ON A.database_id = B.database_id
AND B.name = @DBName
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH

一些常用的SQL(05版以上)資料庫維護指令碼

相關文章