Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.columns

  No additional text.


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

 
Last revision 2008RTM
See also

  all_columns (View)
change_tracking_tables (View)
COLUMN_DOMAIN_USAGE (View)
COLUMN_PRIVILEGES (View)
computed_columns (View)
CONSTRAINT_COLUMN_USAGE (View)
dm_xe_object_columns (View)
dm_xe_session_object_columns (View)
foreign_key_columns (View)
fulltext_index_columns (View)
function_order_columns (View)
identity_columns (View)
index_columns (View)
KEY_COLUMN_USAGE (View)
resource_governor_resource_pools (View)
resource_governor_workload_groups (View)
ROUTINE_COLUMNS (View)
spatial_index_tessellations (View)
spt_columns_odbc_view (View)
spt_columns_view (View)
spt_columns_view_managed (View)
spt_column_privileges_owner_view (View)
spt_column_privileges_set_view (View)
spt_indexcolumns_view_managed (View)
spt_indexes_view (View)
spt_indexes_view_100 (View)
spt_indexes_view_managed (View)
spt_primary_keys_view (View)
spt_procedure_params_view (View)
spt_sparse_columns_view (View)
spt_sproc_columns_odbc_view (View)
spt_sproc_columns_return_values_odbc_view (View)
spt_tablecollations_view (View)
spt_table_type_columns_view (View)
spt_table_type_primary_keys_view (View)
sp_addmergearticle (Procedure)
sp_addmergefilter (Procedure)
sp_addmergepublication (Procedure)
sp_addmergesubscription (Procedure)
sp_addqueued_artinfo (Procedure)
sp_addsynctriggers (Procedure)
sp_add_alert_internal (Procedure)
sp_bindefault (Procedure)
sp_bindrule (Procedure)
sp_cdc_captured_columns (Procedure)
sp_cdc_change_data_capture (Procedure)
sp_cdc_create_change_enumeration_functions (Procedure)
sp_cdc_create_change_enumeration_wrapper_function (Procedure)
sp_cdc_create_change_table (Procedure)
sp_cdc_create_change_table_index (Procedure)
sp_cdc_create_objects (Procedure)
sp_cdc_create_populate_stored_procs (Procedure)
sp_cdc_ddl_event_internal (Procedure)
sp_cdc_disable_table_instance (Procedure)
sp_cdc_disable_table_internal (Procedure)
sp_cdc_enable_table_internal (Procedure)
sp_cdc_get_captured_columns (Procedure)
sp_cdc_logddl_internal (Procedure)
sp_cdc_parse_captured_column_list (Procedure)
sp_cdc_parse_included_column_list (Procedure)
sp_cdc_parse_update_flag_list (Procedure)
sp_cdc_verify_index_name (Procedure)
sp_cdc_vupgrade (Procedure)
sp_changemergearticle (Procedure)
sp_changemergefilter (Procedure)
sp_changemergesubscription (Procedure)
sp_columns (Procedure)
sp_columns_100 (Procedure)
sp_columns_100_rowset (Procedure)
sp_columns_100_rowset2 (Procedure)
sp_columns_90 (Procedure)
sp_columns_90_rowset (Procedure)
sp_columns_90_rowset2 (Procedure)
sp_columns_90_rowset_rmt (Procedure)
sp_columns_ex (Procedure)
sp_columns_ex_100 (Procedure)
sp_columns_ex_90 (Procedure)
sp_columns_managed (Procedure)
sp_columns_rowset (Procedure)
sp_columns_rowset2 (Procedure)
sp_columns_rowset_rmt (Procedure)
sp_column_privileges (Procedure)
sp_constr_col_usage_rowset (Procedure)
sp_constr_col_usage_rowset2 (Procedure)
sp_createstats (Procedure)
sp_dbcmptlevel (Procedure)
sp_ddopen (Procedure)
sp_decimal_estimate_savings_for_table (Procedure)
sp_describe_cursor_columns (Procedure)
sp_estimate_data_compression_savings (Procedure)
sp_extended_logging (Procedure)
sp_fkeys (Procedure)
sp_foreign_keys_rowset (Procedure)
sp_foreign_keys_rowset2 (Procedure)
sp_foreign_keys_rowset3 (Procedure)
sp_foreign_keys_rowset_rmt (Procedure)
sp_fulltext_column (Procedure)
sp_getarticlepkcolbitmap (Procedure)
sp_getqueuedrows (Procedure)
sp_get_mergepublishedarticleproperties (Procedure)
sp_help (Procedure)
sp_helparticlecolumns (Procedure)
sp_helpconstraint (Procedure)
sp_helpdistributor (Procedure)
sp_helpmergearticle (Procedure)
sp_helpmergearticlecolumn (Procedure)
sp_helpmergearticleconflicts (Procedure)
sp_helpmergeconflictrows (Procedure)
sp_helpmergepartition (Procedure)
sp_helprotect (Procedure)
sp_helptext (Procedure)
sp_help_alert (Procedure)
sp_help_fulltext_columns (Procedure)
sp_help_fulltext_columns_cursor (Procedure)
sp_help_fulltext_tables (Procedure)
sp_help_fulltext_tables_cursor (Procedure)
sp_help_notification (Procedure)
sp_help_spatial_geography_index_helper (Procedure)
sp_help_spatial_geometry_index_helper (Procedure)
sp_identitycolumnforreplication (Procedure)
sp_IHaddarticle (Procedure)
sp_IHarticlecolumn (Procedure)
sp_IHarticleview (Procedure)
sp_IHdroparticle (Procedure)
sp_IHdroppublisher (Procedure)
sp_IHflushmetadata (Procedure)
sp_IHget_colinfo (Procedure)
sp_IHhelparticle (Procedure)
sp_IHisallreplcolpk (Procedure)
sp_IHisarticlecolbitset (Procedure)
sp_IHscriptindexes (Procedure)
sp_IHscriptpkwhereclause (Procedure)
sp_IHscriptprimarykey (Procedure)
sp_IHscriptreconwhereclause (Procedure)
sp_IHscripttable (Procedure)
sp_IHscriptupdateparams (Procedure)
sp_IHsyncmetadata (Procedure)
sp_IHValidateRowFilter (Procedure)
sp_IHVerifyConstraint (Procedure)
sp_IHVerifyIndex (Procedure)
sp_IH_LR_GetCacheData (Procedure)
sp_indexcolumns_managed (Procedure)
sp_indexes_90_rowset_rmt (Procedure)
sp_indexes_rowset (Procedure)
sp_indexes_rowset_rmt (Procedure)
sp_isarticlecolbitset (Procedure)
sp_ivindexhasnullcols (Procedure)
sp_mergearticlecolumn (Procedure)
sp_MSaddguidcolumn (Procedure)
sp_MSaddguidindex (Procedure)
sp_MSaddinitialarticle (Procedure)
sp_MSaddlightweightmergearticle (Procedure)
sp_MSaddmergetriggers (Procedure)
sp_MSaddmergetriggers_from_template (Procedure)
sp_MSaddmergetriggers_internal (Procedure)
sp_MSaddsubscriptionarticles (Procedure)
sp_MSaddupdatetrigger (Procedure)
sp_MSadd_filteringcolumn (Procedure)
sp_MSadd_merge_partition_column (Procedure)
sp_MSAdjustConflictTable (Procedure)
sp_MSareallcolpkcomputed (Procedure)
sp_MSarticlecleanup (Procedure)
sp_MSarticlecol (Procedure)
sp_MSarticlecolstatus (Procedure)
sp_MSarticle_synctran_commands (Procedure)
sp_MSarticle_validation (Procedure)
sp_MSbuild_single_post (Procedure)
sp_MSchange_mergearticle (Procedure)
sp_MScheck_article_order (Procedure)
sp_MScheck_autoident_parameters (Procedure)
sp_MScheck_blob_filter_columns (Procedure)
sp_MScheck_dynamic_filters (Procedure)
sp_MScleandbobjectsforreplication (Procedure)
sp_MSclearcolumnbit (Procedure)
sp_MScompute_maxmin_identity (Procedure)
sp_MScreateandsetarticleprocs (Procedure)
sp_MScreatebeforetable (Procedure)
sp_MScreatedupkeyupdatequery (Procedure)
sp_MScreatelightweightdeleteproc (Procedure)
sp_MScreatelightweightinsertorupdatetriggers (Procedure)
sp_MScreatelightweightinsertproc (Procedure)
sp_MScreatelightweightmetatables (Procedure)
sp_MScreatelightweightmultipurposeproc (Procedure)
sp_MScreatelightweighttriggers (Procedure)
sp_MScreatelightweightupdateproc (Procedure)
sp_MScreatenewreplnick (Procedure)
sp_MScreate_article_logical_record_views (Procedure)
sp_MScreate_common_dist_tables (Procedure)
sp_MScreate_dist_tables (Procedure)
sp_MScreate_mergesystables (Procedure)
sp_MScreate_pub_tables (Procedure)
sp_MScreate_sub_tables_internal (Procedure)
sp_MScreate_sysmergeextendedarticlesview (Procedure)
sp_MSdeletelightweightsubscription (Procedure)
sp_MSdependencies (Procedure)
sp_MSdropfkreferencingarticle (Procedure)
sp_MSdropguidcolumn (Procedure)
sp_MSdropmergearticle (Procedure)
sp_MSdrop_cdc (Procedure)
sp_MSdrop_mergesystables (Procedure)
sp_MSdrop_pub_tables (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSdrop_rladmin (Procedure)
sp_MSdummyupdatelightweight (Procedure)
sp_MSenableautonosync (Procedure)
sp_MSenumarticleslightweight (Procedure)
sp_MSenumchangesdirect (Procedure)
sp_MSenumchanges_belongtopartition (Procedure)
sp_MSenumcolumns (Procedure)
sp_MSenumcolumnslightweight (Procedure)
sp_MSenumdistributionagentproperties (Procedure)
sp_MSenumpartialchangesdirect (Procedure)
sp_MSenum_merge_agent_properties (Procedure)
sp_MSestimatemergesnapshotworkload (Procedure)
sp_MSevaluate_logicalrecordparent (Procedure)
sp_MSevaluate_logicalrecordparent_allcontentsrows (Procedure)
sp_MSfillupmissingcols (Procedure)
sp_MSfillup_deleted_cols (Procedure)
sp_MSFixPubColumnBitmaps (Procedure)
sp_MSfixup_base_columns (Procedure)
sp_MSfixup_defaults_name (Procedure)
sp_MSfixup_single_artddls (Procedure)
sp_MSfixup_single_ddls (Procedure)
sp_MSforcereenumeration (Procedure)
sp_MSforeach_worker (Procedure)
sp_MSgenerateexpandproc (Procedure)
sp_MSgeneratenosyncsubscriptionsetupscript (Procedure)
sp_MSgenerate_articlechangemembershipevaluation_proc (Procedure)
sp_MSgenerate_enumchangedcolumnvalues_query (Procedure)
sp_MSgen_sync_tran_procs (Procedure)
sp_MSgetbeforetableinsert (Procedure)
sp_MSgetcolordinalfromcolname (Procedure)
sp_MSgetcolumnlist (Procedure)
sp_MSgetconflictinsertproc (Procedure)
sp_MSgetfiltercolumnlist (Procedure)
sp_MSgetfilteringcolumns (Procedure)
sp_MSgetlightweightmetadatabatch (Procedure)
sp_MSgetmissingbm (Procedure)
sp_MSgetreplicainfo (Procedure)
sp_MSgetrowmetadatalightweight (Procedure)
sp_MSgettablecontents (Procedure)
sp_MSgettrancftsrcrow (Procedure)
sp_MSgettransarticlecolumnlist (Procedure)
sp_MSgetviewcolumnlist (Procedure)
sp_MSget_colinfo (Procedure)
sp_MSget_col_position (Procedure)
sp_MSget_effective_pub_compat_level (Procedure)
sp_MSget_identity_range_info (Procedure)
sp_MSget_load_hint (Procedure)
sp_MSget_MSmerge_rowtrack_colinfo (Procedure)
sp_MSget_repl_commands (Procedure)
sp_MSget_synctran_column (Procedure)
sp_MSget_type (Procedure)
sp_MShelpalterbeforetable (Procedure)
sp_MShelpcolumns (Procedure)
sp_MShelpcreatebeforetable (Procedure)
sp_MShelpfulltextindex (Procedure)
sp_MShelpmergearticles_nonpartgroups (Procedure)
sp_MShelpmergeconflictcounts (Procedure)
sp_MShelpmergepub_withoutrownumbers (Procedure)
sp_MShelpmergepub_withrownumbers (Procedure)
sp_MShelpmergeselectarticles (Procedure)
sp_MShelp_identity_property (Procedure)
sp_MShelp_publication (Procedure)
sp_MShelp_replication_table (Procedure)
sp_MSindexcolfrombin (Procedure)
sp_MSinsertbeforeimageclause (Procedure)
sp_MSisallreplcolpk (Procedure)
sp_MSiscolpk (Procedure)
sp_MSisnonpkukupdateinconflict (Procedure)
sp_MSispkupdateinconflict (Procedure)
sp_MSis_identity_insert (Procedure)
sp_MSis_joinfilter_based_on_PK_UQ_constraints (Procedure)
sp_MSmakearticleprocs (Procedure)
sp_MSmakebatchinsertproc (Procedure)
sp_MSmakebatchupdateproc (Procedure)
sp_MSmakeconflictinsertproc (Procedure)
sp_MSmakeconflicttable (Procedure)
sp_MSmakectsview (Procedure)
sp_MSmakedeleteproc (Procedure)
sp_MSmakedynsnapshotvws (Procedure)
sp_MSmakeexpandproc (Procedure)
sp_MSmakeinsertproc (Procedure)
sp_MSmakemetadataselectproc (Procedure)
sp_MSmakeorcheck_joinfilter_using_dri (Procedure)
sp_MSmakepeerconflicttable (Procedure)
sp_MSmakeselectproc (Procedure)
sp_MSmakesystableviews (Procedure)
sp_MSmaketrancftproc (Procedure)
sp_MSmakeupdateproc (Procedure)
sp_MSmatchkey (Procedure)
sp_MSmergefixup_defaults_name (Procedure)
sp_MSmerge_altertable (Procedure)
sp_MSmerge_create_sub_table (Procedure)
sp_MSmerge_dropindex (Procedure)
sp_MSmerge_gen_articlecolumnrowset (Procedure)
sp_MSmerge_is_snapshot_required (Procedure)
sp_MSmerge_metadataupgrade (Procedure)
sp_MSmerge_subscriptions_upgrade (Procedure)
sp_MSmerge_upgrade_from_90rtm (Procedure)
sp_MSmodify_boolfilterclause (Procedure)
sp_MSNonSQLDDL (Procedure)
sp_MSobjectprivs (Procedure)
sp_MSproxiedmetadatalightweight (Procedure)
sp_MSpublicationview (Procedure)
sp_MSpublication_validation (Procedure)
sp_MSrefresh_idrange_check_constraint (Procedure)
sp_MSreplsup_table_has_pk (Procedure)
sp_MSrepl_addarticle (Procedure)
sp_MSrepl_articlecolumn (Procedure)
sp_MSrepl_changearticle (Procedure)
sp_MSrepl_changearticlecolumndatatype (Procedure)
sp_MSrepl_changepublication (Procedure)
sp_MSrepl_changesubstatus (Procedure)
sp_MSrepl_droparticle (Procedure)
sp_MSrepl_enable_articles_for_het_sub (Procedure)
sp_MSrepl_getpkfkrelation (Procedure)
sp_MSrepl_get_Oracle_publisher_metadata (Procedure)
sp_MSrepl_helparticle (Procedure)
sp_MSrepl_helparticlecolumns (Procedure)
sp_MSrepl_refresh_heterogeneous_publisher (Procedure)
sp_MSrepl_schema (Procedure)
sp_MSrepl_snapshot_helparticlecolumns (Procedure)
sp_MSreseed (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_MSrestoresavedforeignkeys (Procedure)
sp_MSscriptforeignkeyrestore (Procedure)
sp_MSscriptsubscriberprocs (Procedure)
sp_MSscript_article_view (Procedure)
sp_MSscript_beginproc (Procedure)
sp_MSscript_begintrig1 (Procedure)
sp_MSscript_compensating_insert (Procedure)
sp_MSscript_insert_statement (Procedure)
sp_MSscript_params (Procedure)
sp_MSscript_pkvar_assignment (Procedure)
sp_MSscript_pub_upd_trig (Procedure)
sp_MSscript_trigger_assignment (Procedure)
sp_MSscript_trigger_update_checks (Procedure)
sp_MSscript_trigger_variables (Procedure)
sp_MSscript_update_statement (Procedure)
sp_MSscript_where_clause (Procedure)
sp_MSsetartprocs (Procedure)
sp_MSsetconflicttable (Procedure)
sp_MSsetfilteredstatus (Procedure)
sp_MSSetLogForRepl (Procedure)
sp_MSsetrowmetadata (Procedure)
sp_MSsetupbelongs (Procedure)
sp_MSsetup_function_list_table (Procedure)
sp_MSsetup_partition_groups (Procedure)
sp_MSsetup_publisher_idrange (Procedure)
sp_MSsetup_use_partition_groups (Procedure)
sp_MSset_timestamp_bm (Procedure)
sp_MStablekeys (Procedure)
sp_MStable_not_modifiable (Procedure)
sp_MStable_validation (Procedure)
sp_MStran_altertable (Procedure)
sp_MStran_autoproc (Procedure)
sp_MSuniquecolname (Procedure)
sp_MSunmarkreplinfo (Procedure)
sp_MSupdatesysmergearticles (Procedure)
sp_MSUpgradeConflictTable (Procedure)
sp_MSupgrade_merge_history90 (Procedure)
sp_MSvalidatearticle (Procedure)
sp_MSvalidatepeertopeerarticles (Procedure)
sp_MSverboselogging (Procedure)
sp_ORAaddarticle (Procedure)
sp_ORACheckSourceTable (Procedure)
sp_ORAenumtablecolumninfo (Procedure)
sp_ORAgetcolumndata (Procedure)
sp_ORAgetversion (Procedure)
sp_ORAhelparticle (Procedure)
sp_ORAremotequery (Procedure)
sp_ORAvalidatecache (Procedure)
sp_ORAValidateRowFilter (Procedure)
sp_pkeys (Procedure)
sp_refreshreplsysservers (Procedure)
sp_rename (Procedure)
sp_repladdcolumn (Procedure)
sp_repldropcolumn (Procedure)
sp_replmonitoralerts (Procedure)
sp_replmonitorhelppublication (Procedure)
sp_replmonitorhelppublisherhelper (Procedure)
sp_replscriptuniquekeywhereclause (Procedure)
sp_repltablehasnonpkuniquekey (Procedure)
sp_restoredbreplication (Procedure)
sp_scriptdelproccore (Procedure)
sp_scriptinsproccore (Procedure)
sp_scriptpkwhereclause (Procedure)
sp_scriptpubwinsrefreshcursorvars (Procedure)
sp_scriptreconwhereclause (Procedure)
sp_scriptupdateparams (Procedure)
sp_scriptupdproccore (Procedure)
sp_script_insertforcftresolution (Procedure)
sp_showrowreplicainfo (Procedure)
sp_sparse_columns_100_rowset (Procedure)
sp_special_columns (Procedure)
sp_special_columns_100 (Procedure)
sp_special_columns_90 (Procedure)
sp_sproc_columns (Procedure)
sp_sproc_columns_100 (Procedure)
sp_sproc_columns_90 (Procedure)
sp_statistics (Procedure)
sp_statistics_100 (Procedure)
sp_subscription_cleanup (Procedure)
sp_sysutility_mi_collect_dac_execution_statistics_internal (Procedure)
sp_tables (Procedure)
sp_tables_rowset (Procedure)
sp_tables_rowset_rmt (Procedure)
sp_table_type_columns_100 (Procedure)
sp_table_type_columns_100_rowset (Procedure)
sp_table_validation (Procedure)
sp_unbindefault (Procedure)
sp_unbindrule (Procedure)
sp_unlink_publication_internal (Procedure)
sp_update_alert (Procedure)
sp_upgrade_log_shipping (Procedure)
sp_vupgrade_distdb (Procedure)
sp_vupgrade_mergetables (Procedure)
sp_vupgrade_MSsubscription_properties (Procedure)
sp_vupgrade_publisherdb (Procedure)
sp_vupgrade_replmsdb (Procedure)
sp_vupgrade_replsecurity_metadata (Procedure)
sp_vupgrade_subpass (Procedure)
sp_vupgrade_subscription_databases (Procedure)
sp_vupgrade_subscription_tables (Procedure)
sp_vupgrade_sysarticlecolumns (Procedure)
sp_vupgrade_syscol_status (Procedure)
sp_vupgrade_updatingpublicationarticle (Procedure)
stats_columns (View)
syscolumns (View)
syscomments (View)
syscursorcolumns (View)
sysdepends (View)
sysforeignkeys (View)
sysindexkeys (View)
sysmaintplan_plans (View)
sysprotects (View)
system_columns (View)
system_internals_partition_columns (View)
TABLE_PRIVILEGES (View)
trace_columns (View)
VIEW_COLUMN_USAGE (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash