Getting System Defined View Text in SQL Server

Returns one row per system object that contains an SQL language-defined module. System objects of type FN, IF, P, PC, TF, V have an associated SQL module. 

Getting System Defined View text:

select object_name(m.object_id), * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='V'


select object_name(m.object_id) as name, * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='V' and name='systypes'


SQL Text of "systypes":

CREATE VIEW sys.systypes AS   SELECT    name,    xtype = system_type_id,    status = convert(tinyint, 1 - is_nullable),    xusertype = convert(smallint, user_type_id),    length = max_length,    xprec = precision,    xscale = scale,    tdefault = default_object_id,    domain = rule_object_id,    uid = convert(smallint, schema_id),    reserved = convert(smallint, 0),    collationid = convert(int, collationproperty(collation_name, 'collationid')),    usertype = convert(smallint, typepropertyex(user_type_id, 'oldusertype')),    variable = sysconv(bit, -- nvarchar, varchar or varbinary     case when system_type_id in (165, 167, 231) then 1 else 0 end),    allownulls = is_nullable,    type = xtypetotds(system_type_id, 0),    printfmt = convert(varchar(255), null),    prec = convert(smallint, -- ntext, image or text     case when system_type_id not in (34, 35, 99) then typepropertyex(user_type_id, 'precision') end),    scale = convert(tinyint, typepropertyex(user_type_id, 'scale')),    collation = collation_name   FROM sys.types  

Thanks :)
Ebook Download
View all
Learn
View all