I am checking system base table at https://msdn.microsoft.com/en-us/library/ms179503.aspx and system internal views at https://technet.microsoft.com/en-us/library/ms189600(v=sql.105).aspx

For base table sys.syscolpars, is there a corresponding system view for it so that I can access its data without loginning in DAC mode?


  • What information in sys.syscolpars are you looking fro that you can't find in other system views? – Mikael Eriksson Dec 16 '16 at 7:07

The closest equivalent is sys.columns.

You can use

exec sys.sp_helptext 'sys.columns'

to see the underlying query used and how the columns are mapped.

CREATE VIEW sys.columns
  SELECT c.id                                                             AS object_id,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL  
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM  
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL  
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL  
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED  
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM  
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT  
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB  
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL  
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS  
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC   
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE  
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET  
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.