April 17, 2012

sp_db_vardecimal_storage_format (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_db_vardecimal_storage_format(nvarchar @dbname
, varchar @vardecimal_storage_format)

MetaData:

 create procedure sys.sp_db_vardecimal_storage_format      
@dbname sysname = NULL, -- database name to change
@vardecimal_storage_format varchar(3) = NULL -- vardecimal format to turn on/off
as
set nocount on

-- If db name is not given, enumerate all databases and print out their vardecimal state
if @dbname is null
begin
select name as 'Database Name',
(case
when name in ('master', 'tempdb', 'model', 'msdb') then 'OFF'
else 'ON'
end) as 'Vardecimal State'
from master.dbo.sysdatabases

return (0)
end

-- Verify the database name and get info
declare @dbid int
select @dbid = dbid
from master.dbo.sysdatabases
where name = @dbname

-- If @dbname not found, say so and list the databases.
if @dbid is null
begin
raiserror(15010,-1,-1,@dbname)
print ' '
select 'Available databases:' = name
from master.dbo.sysdatabases
return (1)
end

-- The value specified must be 'ON' or 'OFF'.
if @vardecimal_storage_format is not null and lower(@vardecimal_storage_format) not in ('on', 'off')
begin
raiserror(15229,-1,-1)
return (1)
end

-- Determine whether it's a system database.
declare @is_system_db bit
if @dbname in ('master', 'tempdb', 'model', 'msdb')
set @is_system_db = 1
else
set @is_system_db = 0

if @vardecimal_storage_format is null
begin
-- Return ON or OFF based on whether the db is a system database.
select @dbname as 'Database Name',
(case
when @is_system_db = 1 then 'OFF'
else 'ON'
end) as 'Vardecimal State'

return (0)
end

-- Not supported in system databases.
if @is_system_db = 1
begin
raiserror(15657, -1, -1, @dbname)
return (1)
end

return (0) -- sp_db_vardecimal_storage_format

No comments:

Post a Comment

Total Pageviews