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