June 8, 2012

sp_schemata_rowset (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_schemata_rowset(nvarchar @schema_name
, nvarchar @schema_owner)

MetaData:

   
create procedure sys.sp_schemata_rowset
(
@schema_name sysname = null,
@schema_owner sysname = null
)
as
select distinct
CATALOG_NAME = db_name(),
SCHEMA_NAME = s.name,
SCHEMA_OWNER = s.name,
DEFAULT_CHARACTER_SET_CATALOG = convert(sysname,N'master'),
DEFAULT_CHARACTER_SET_SCHEMA = convert(sysname,N'dbo'),
DEFAULT_CHARACTER_SET_NAME = convert(sysname,CollationPropertyFromId(-1, 'sqlcharsetname'))

from
sys.schemas s inner join
sys.all_objects o on
(
o.schema_id = s.schema_id and
(@schema_name is null or schema_id(@schema_name) = o.schema_id) and
(@schema_owner is null or schema_id(@schema_owner) = o.schema_id)
)

order by 2

No comments:

Post a Comment

Total Pageviews