April 18, 2012

sp_detach_db (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_detach_db(nvarchar @dbname
, nvarchar @skipchecks
, nvarchar @keepfulltextindexfile)

MetaData:

 create procedure sys.sp_detach_db  
@dbname sysname = null,
@skipchecks nvarchar(10) = null,
@keepfulltextindexfile nvarchar(10) = null
as
declare @dbid int
declare @exec_stmt nvarchar(4000)

if (SERVERPROPERTY('IsMatrix') = 1)
begin
raiserror (28401, -1, -1, N'sys.sp_detach_db')
return (1)
end

if @dbname is null
begin
raiserror(15354,-1,-1)
return(1)
end

if lower(@skipchecks) <> N'true'
and lower(@skipchecks) <> N'false'
and @skipchecks is not null
begin
raiserror(15354,-1,-1)
return(1)
end

if @keepfulltextindexfile is not null
and lower(@keepfulltextindexfile) <> N'true'
and lower(@keepfulltextindexfile) <> N'false'
begin
raiserror(15354,-1,-1)
return(1)
end

select @dbid = dbid from master.dbo.sysdatabases where name=@dbname
if @dbid is null
begin
raiserror(15010,-1,-1,@dbname)
return(1)
end

-- make sure not trying to detach within a transaction
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_detach_db')
return(1)
end

-- run UPDATE STATISTICS on all tables in the database so they are current
-- when transferred to READONLY media
if lower(@skipchecks) <> N'true'
begin
select @exec_stmt = 'USE ' + quotename( @dbname , '[')
+ ' EXEC sys.sp_updatestats ''RESAMPLE'' '
EXEC (@exec_stmt)
end


if @keepfulltextindexfile is null
begin
select @exec_stmt = 'DBCC DETACHDB ('
+ quotename( @dbname , '[')
+ ')'
end
else
begin
if lower(@keepfulltextindexfile) <> N'true'
begin
select @exec_stmt = 'DBCC DETACHDB ('
+ quotename( @dbname , '[')
+ ', 0 )'
end
else
begin
select @exec_stmt = 'DBCC DETACHDB ('
+ quotename( @dbname , '[')
+ ', 1 )'
end
end

EXEC (@exec_stmt)
return (0) -- sp_detach_db

No comments:

Post a Comment

Total Pageviews