May 16, 2012

sp_MShelpcreatebeforetable (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_MShelpcreatebeforetable(int @objid
, nvarchar @newname)

MetaData:

 create procedure sys.sp_MShelpcreatebeforetable  
@objid int,
@newname sysname
AS
declare @command nvarchar(1000)
declare @retcode int
declare @include int
declare @tablenick int
declare @colname nvarchar(140)
declare @typename nvarchar(258)
declare @base_typename nvarchar(140)
declare @schname nvarchar(140)
declare @system_type_id int
declare @colid smallint
declare @colidstr nvarchar(5)
declare @len smallint
declare @prec smallint
declare @scale int
declare @isnullable tinyint
declare @cMaxIndexLength int

set @cMaxIndexLength= 900 -- max index column size in SQL 2000

set nocount on

-- Security check: dbo and sysadmin only
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
select 'a' = 'raiserror (''Error creating before image table'' , 16, -1)'
return (1)
end

select @tablenick = max(nickname) from dbo.sysmergearticles where objid = @objid
if @tablenick is null
begin
select 'a' = 'raiserror (''Error creating before image table'' , 16, -1)'
return (1)
end

-- create temp table to select the command text out of
declare @tempcmd table (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)

set @command = 'create table dbo.' + quotename(@newname) + '('
insert into @tempcmd (phase, cmdtext) values (1, @command)

-- Loop over the columns and see which ones we include

declare col_cursor CURSOR LOCAL FAST_FORWARD
for select C.name, type_name(C.user_type_id), type_name(C.system_type_id),
case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
C.system_type_id, C.max_length, C.precision, C.scale, C.is_nullable, C.column_id
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @objid and C.is_computed <> 1 and C.user_type_id <> type_id('timestamp') order by C.column_id
FOR READ ONLY

open col_cursor
fetch next from col_cursor into @colname, @typename, @base_typename, @schname, @system_type_id, @len, @prec, @scale, @isnullable, @colid
while (@@fetch_status <> -1)
begin
set @include = 0

if columnproperty( @objid, @colname , 'isrowguidcol')=1
begin
set @include = 1
end
else
begin
-- does updating this column change membership in a partial replica?
if exists (select * from dbo.sysmergearticles
where objid = @objid and sys.fn_MSisfilteredcolumn(subset_filterclause, @colname, @objid) = 1)
set @include = 1
else if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1)
set @include = 1
else if exists (select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1)
set @include = 1
end
-- If we want this column, map its type and insert a row to temp table
if @include = 1
begin
if ((@base_typename='nvarchar' or @base_typename='nchar') and @len <> -1) -- a unit of nchar takes 2 bytes
set @len = @len/2
exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
if @@ERROR<>0 or @retcode<>0 goto Failure
if (sys.fn_IsTypeBlob(@system_type_id, @len) = 0)
begin
select @colname = N'[' + replace(@colname, N']', N']]') + N']'

if @isnullable = 1
set @command = @colname + ' ' + @schname + @typename + ' NULL, '
else
set @command = @colname + ' ' + @schname + @typename + ' NOT NULL, '

-- Insert the part of create table command for this column
insert into @tempcmd (phase, cmdtext) values (1, @command)

-- Insert a create index command if column is not too long
if (@len <= @cMaxIndexLength)
begin
set @colidstr =convert(nvarchar(4), @colid)
set @command = 'create index ' + quotename(@newname + '_' + @colidstr) + ' on ' + quotename(@newname) + ' (' + @colname + ') '
insert into @tempcmd (phase, cmdtext) values (2, @command)
end
end
end
-- Repeat the loop with next column --
fetch next from col_cursor into @colname, @typename, @base_typename, @schname, @system_type_id, @len, @prec, @scale, @isnullable, @colid
end

close col_cursor
deallocate col_cursor

-- Insert last column, generation
set @command = 'generation bigint NOT NULL, system_delete bit default(0))
'

insert into @tempcmd (phase, cmdtext) values (1, @command)
set @command = 'create clustered index ' + quotename(@newname + '_gen') + ' on ' + quotename(@newname) + '(generation)
'

insert into @tempcmd (phase, cmdtext) values (2, @command)

select cmdtext from @tempcmd order by phase, step
-- drop table @tempcmd

return(0)
Failure:
close col_cursor
deallocate col_cursor
return(1)

No comments:

Post a Comment

Total Pageviews