April 17, 2012

sp_create_removable (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_create_removable(nvarchar @dbname
, nvarchar @syslogical
, nvarchar @sysphysical
, int @syssize
, nvarchar @loglogical
, nvarchar @logphysical
, int @logsize
, nvarchar @datalogical1
, nvarchar @dataphysical1
, int @datasize1
, nvarchar @datalogical2
, nvarchar @dataphysical2
, int @datasize2
, nvarchar @datalogical3
, nvarchar @dataphysical3
, int @datasize3
, nvarchar @datalogical4
, nvarchar @dataphysical4
, int @datasize4
, nvarchar @datalogical5
, nvarchar @dataphysical5
, int @datasize5
, nvarchar @datalogical6
, nvarchar @dataphysical6
, int @datasize6
, nvarchar @datalogical7
, nvarchar @dataphysical7
, int @datasize7
, nvarchar @datalogical8
, nvarchar @dataphysical8
, int @datasize8
, nvarchar @datalogical9
, nvarchar @dataphysical9
, int @datasize9
, nvarchar @datalogical10
, nvarchar @dataphysical10
, int @datasize10
, nvarchar @datalogical11
, nvarchar @dataphysical11
, int @datasize11
, nvarchar @datalogical12
, nvarchar @dataphysical12
, int @datasize12
, nvarchar @datalogical13
, nvarchar @dataphysical13
, int @datasize13
, nvarchar @datalogical14
, nvarchar @dataphysical14
, int @datasize14
, nvarchar @datalogical15
, nvarchar @dataphysical15
, int @datasize15
, nvarchar @datalogical16
, nvarchar @dataphysical16
, int @datasize16)

MetaData:

 create procedure sys.sp_create_removable  
@dbname sysname = null, -- name of db
@syslogical sysname = null, -- logical name of system device
@sysphysical nvarchar (260) = null, -- physical name of system device
@syssize int = null, -- size of sys device in Meg.
@loglogical sysname = null, -- logical name of log device
@logphysical nvarchar (260) = null, -- physical name of log device
@logsize int = null, -- size of log device in Meg.
@datalogical1 sysname = null, -- logical name of data device
@dataphysical1 nvarchar (260) = null, -- physical name of data device
@datasize1 int = null, -- size of data device in Meg.
@datalogical2 sysname = null, -- logical name of data device
@dataphysical2 nvarchar (260) = null, -- physical name of data device
@datasize2 int = null, -- size of data device in Meg.
@datalogical3 sysname = null, -- logical name of data device
@dataphysical3 nvarchar (260) = null, -- physical name of data device
@datasize3 int = null, -- size of data device in Meg.
@datalogical4 sysname = null, -- logical name of data device
@dataphysical4 nvarchar (260) = null, -- physical name of data device
@datasize4 int = null, -- size of data device in Meg.
@datalogical5 sysname = null, -- logical name of data device
@dataphysical5 nvarchar (260) = null, -- physical name of data device
@datasize5 int = null, -- size of data device in Meg.
@datalogical6 sysname = null, -- logical name of data device
@dataphysical6 nvarchar (260) = null, -- physical name of data device
@datasize6 int = null, -- size of data device in Meg.
@datalogical7 sysname = null, -- logical name of data device
@dataphysical7 nvarchar (260) = null, -- physical name of data device
@datasize7 int = null, -- size of data device in Meg.
@datalogical8 sysname = null, -- logical name of data device
@dataphysical8 nvarchar (260) = null, -- physical name of data device
@datasize8 int = null, -- size of data device in Meg.
@datalogical9 sysname = null, -- logical name of data device
@dataphysical9 nvarchar (260) = null, -- physical name of data device
@datasize9 int = null, -- size of data device in Meg.
@datalogical10 sysname = null, -- logical name of data device
@dataphysical10 nvarchar (260) = null, -- physical name of data device
@datasize10 int = null, -- size of data device in Meg.
@datalogical11 sysname = null, -- logical name of data device
@dataphysical11 nvarchar (260) = null, -- physical name of data device
@datasize11 int = null, -- size of data device in Meg.
@datalogical12 sysname = null, -- logical name of data device
@dataphysical12 nvarchar (260) = null, -- physical name of data device
@datasize12 int = null, -- size of data device in Meg.
@datalogical13 sysname = null, -- logical name of data device
@dataphysical13 nvarchar (260) = null, -- physical name of data device
@datasize13 int = null, -- size of data device in Meg.
@datalogical14 sysname = null, -- logical name of data device
@dataphysical14 nvarchar (260) = null, -- physical name of data device
@datasize14 int = null, -- size of data device in Meg.
@datalogical15 sysname = null, -- logical name of data device
@dataphysical15 nvarchar (260) = null, -- physical name of data device
@datasize15 int = null, -- size of data device in Meg.
@datalogical16 sysname = null, -- logical name of data device
@dataphysical16 nvarchar (260) = null, -- physical name of data device
@datasize16 int = null -- size of data device in Meg.
as
declare @retcode int,
@exec_str nvarchar (max),
@numdevs int

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

if (not (is_srvrolemember('sysadmin') = 1)) -- Make sure that it's the SA executing this.
begin
raiserror(15247,-1,-1)
return(1)
end

if @dbname is null
or @syslogical is null
or @sysphysical is null
or @syssize is null
or @loglogical is null
or @logphysical is null
or @logsize is null
or @datalogical1 is null
or @dataphysical1 is null
or @datasize1 is null
begin
raiserror (15261,-1,-1)
return (1)
end

if exists (select * from master.dbo.sysdatabases where name = @dbname)
begin
raiserror(15032,-1,-1,@dbname)
return(1)
end

-- Check to verify that valid sizes were supplied for required devices.
if @syssize < 1 or @logsize < 1 or @datasize1 < 1
begin
raiserror (15262,-1,-1)
return(1)
end

-- Check to see if a valid database name was supplied.
EXEC @retcode = sys.sp_validname @dbname
if @retcode <> 0
return(1)

-- valid syslogical?
EXEC @retcode = sys.sp_validname @syslogical
if @retcode <> 0
return(1)

-- valid loglogical?
EXEC @retcode = sys.sp_validname @loglogical
if @retcode <> 0
return(1)

-- valid datalogical1?
EXEC @retcode = sys.sp_validname @datalogical1
if @retcode <> 0
return(1)

-- Create the database's system device segment.
select @exec_str = 'CREATE DATABASE '
+ quotename( @dbname , '[')
+ ' ON (NAME ='
+ quotename( @syslogical , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@sysphysical ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@syssize)
+ ') LOG ON (NAME='
+ quotename( @loglogical , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@logphysical ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@logsize)
+ ')'
EXEC(@exec_str)

if @@error <> 0
begin
raiserror(15264,-1,-1,'system or log')
return(1)
end

-- Add a filegroup for data
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+ ' ADD FILEGROUP readonlyfilegroup'

EXEC(@exec_str)

if @@error <> 0
begin
raiserror(15264,-1,-1,'user filegroup')
return(1)
end

select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical1 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical1 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize1)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)

if @@error <> 0
begin
raiserror(15264,-1,-1,'user data')
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end

-- Make this the default filegroup
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+ ' MODIFY FILEGROUP readonlyfilegroup DEFAULT'

EXEC(@exec_str)

if @@error <> 0
begin
raiserror(15264,-1,-1,'default filegroup')
return(1)
end

-- Check out optional data devices.
if @datalogical2 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical2 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical2 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize2)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical2)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 2
end
else
goto no_more_devs

if @datalogical3 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical3 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical3 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize3)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical3)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 3
end
else
goto no_more_devs

if @datalogical4 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical4 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical4 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize4)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical4)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 4
end
else
goto no_more_devs

if @datalogical5 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical5 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical5 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize5)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical5)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 5
end
else
goto no_more_devs

if @datalogical6 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical6 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical6 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize6)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical6)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 6
end
else
goto no_more_devs

if @datalogical7 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical7 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical7 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize7)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical7)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 7
end
else
goto no_more_devs

if @datalogical8 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical8 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical8 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize8)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical8)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 8
end
else
goto no_more_devs

if @datalogical9 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical9 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical9 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize9)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical9)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 9
end
else
goto no_more_devs

if @datalogical10 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical10 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical10 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize10)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical10)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 10
end
else
goto no_more_devs

if @datalogical11 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical11 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical11 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize11)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical11)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 11
end
else
goto no_more_devs

if @datalogical12 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical12 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical12 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize12)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical12)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 12
end
else
goto no_more_devs

if @datalogical13 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical13 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical13 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize13)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical13)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 13
end
else
goto no_more_devs

if @datalogical14 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical14 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical14 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize14)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical14)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 14
end
else
goto no_more_devs

if @datalogical15 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical15 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical15 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize15)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical15)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 15
end
else
goto no_more_devs

if @datalogical16 is not null
begin
select @exec_str = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+' ADD FILE (NAME ='
+ quotename( @datalogical16 , '[')
+ ',FILENAME ='
+ ''''
+ REPLACE(@dataphysical16 ,N'''',N'''''')
+ ''''
+ ',SIZE ='
+ convert(varchar(28),@datasize16)
+ ') TO FILEGROUP readonlyfilegroup'
EXEC(@exec_str)
if @retcode <> 0
begin
raiserror(15269,-1,-1,@datalogical16)
select @exec_str = 'DROP DATABASE '
+ quotename( @dbname , '[')
EXEC(@exec_str)
return(1)
end
select @numdevs = 16
end

no_more_devs:
return(0) -- sp_create_removable

No comments:

Post a Comment

Total Pageviews