Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelpcolumns

  No additional text.


Syntax
create procedure sys.sp_MShelpcolumns
@tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0
as

   /* For non-string columns, sp_MShelpcolumns returns the length in syscolumns.length, */
   /* which is defined in BOL as "maximum physical storage length from systypes".       */
   /* For string columns (including types based on string types), sp_MShelpcolumns      */
   /* returns this maximum length in characters (i.e. it returns syscolumns.length      */
   /* adjusted to whether the column is based on char or nchar).                        */

   /*** @flags2 added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MShelpcolumns '%s', null, 'id', 1                           ***/

   create table #sphelpcols
      (
      col_name         nvarchar(128)   COLLATE database_default NOT NULL,
      col_id           int                          NOT NULL,
      col_typename     nvarchar(128)   COLLATE database_default NOT NULL,
      col_len          int                          NOT NULL,
      col_prec         int                          NULL,
      col_scale        int                          NULL,
      col_numtype      smallint                     NOT NULL,  /* For DaVinci to get sp_help-type filtering of prec/scale */
      col_null         bit                          NOT NULL,  /* status & 8 */
      col_identity     bit                          NOT NULL,  /* status & 128 */
      col_defname      nvarchar(257)  COLLATE database_default NULL,      /* fully-qual'd default name, or NULL */
      col_rulname      nvarchar(257)  COLLATE database_default NULL,      /* fully-qual'd rule name, or NULL */
      col_basetypename nvarchar(128)   COLLATE database_default NOT NULL,
      col_flags        int                          NULL,      /* COL_* bits */

/* Fix for Raid # 53682 */
      col_seed         nvarchar (40)      COLLATE database_default NULL,
/*	   col_seed			numeric (28)      NULL,  */
      col_increment    nvarchar (40)      COLLATE database_default NULL,
/*	   col_increment	int 				NULL,  */

      col_dridefname   nvarchar(128)   COLLATE database_default NULL,      /* DRI DEFAULT name */
      col_dridefid     int                          NULL,      /* remember the DRI DEFAULT id in syscomments, so we can retrieve it later */
      col_iscomputed   int                          NOT NULL,
      col_objectid     int                          NOT NULL,  /* column object id, need it to get computed text from syscomments */
      col_NotForRepl   bit                          NOT NULL,  /* Not For Replication setting */
      col_fulltext     bit                          NOT NULL,  /* FullTextIndex setting */
      col_AnsiPad      bit                          NULL,      /* Ansi_Padding setting */
      /* following columns are repeating the info from col_defname and col_rulname                  */
      /* because we can not change data in col_defname and col_rulname, since daVinci is using them */
      col_DOwner       nvarchar(128)   COLLATE database_default NULL,      /* non-DRI DEFAULT owner, or NULL */
      col_DName        nvarchar(128)   COLLATE database_default NULL,      /* non-DRI DEFAULT name, or NULL */
      col_ROwner       nvarchar(128)   COLLATE database_default NULL,      /* non-DRI RULE owner, or NULL */
      col_RName        nvarchar(128)   COLLATE database_default NULL,      /* non-DRI RULE name, or NULL */
      col_collation    nvarchar(128)   COLLATE database_default NULL,      /* column level collation, valid for string columns only */
      col_isindexable  int,
      col_language     int,
      )

/** For DaVinci **/
/** Use sp_help filtering of precision/scale (only fordecimal/numeric types; else use NULL). **/


	if @flags is null
		select @flags = 0
	if (@tablename = N'?')
	begin
		print N''
		print N'Usage:  sp_MShelpcolumns @tablename, @flags int = 0'
		print N' where @flags is a bitmask of:'
		print N' 0x0200		= No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)'
		print N' 0x0400		= UDDTs --> Base type'
		print N' 0x80000		= TimestampToBinary (convert timestamp cols to binary(8))'
		print N' 0x40000000	= No Identity attribute'
		return 0
	end

	declare @objid int
	select @objid = object_id(@tablename)
	if (@objid is null)
	begin
		RAISERROR (15001, -1, -1, @tablename)
		return 1
	end

	set nocount on

   /* Do not store the computed text in this temp table, because one extra join causes big performance hit */
	/* First load stuff so we can blot off inappropriate info and massage as per @flags */
	insert #sphelpcols
		select c.name, c.colid, st.name,
         case when bt.name in (N'nchar', N'nvarchar') then c.length/2 else c.length end,
			ColumnProperty(@objid, c.name, N'Precision'),
			ColumnProperty(@objid, c.name, N'Scale'),
				-- col_numtype for DaVinci:  use sp_help-type prec/scale filtering for @flags2 & 1
			case when (@flags2 & 1 <> 0 and bt.name in (N'tinyint',N'smallint',N'decimal',N'int',N'real',N'money',N'float',N'numeric',N'smallmoney',N'bigint'))
					then 1 else 0 end,
				-- Nullable
			convert(bit, ColumnProperty(@objid, c.name, N'AllowsNull')),
				-- Identity
			case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N'IsIdentity')) else 0 end,
				-- Non-DRI Default (make sure it's not a DRI constraint).
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else schema_name(sysod.schema_id) + N'.' + d.name end,
				-- Non-DRI Rule
			case when (c.domain = 0) then null else schema_name(sysor.schema_id) + N'.' + r.name end,
				-- Physical base datatype
			bt.name,
				-- Initialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
			case when st.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
					when st.name in (N'decimal',N'numeric') then 0x0002
					else 0 end
					-- Will be NULL if column is not UniqueIdentifier.
					+ case isnull(ColumnProperty(@objid, c.name, N'IsRowGuidCol'), 0) when 0 then 0 else 0x0008 end,
				-- Identity seed and increment

/* Fix for Raid # 53682 */
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(@tablename)) else null end,
/*			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_seed(@tablename) else null end,  */
			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_incr(@tablename)) else null end,
/*			case when (ColumnProperty(@objid, c.name, N'IsIdentity') <> 0) then ident_incr(@tablename) else null end,  */

				-- DRI Default name
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then object_name(c.cdefault) else null end,
				-- DRI Default text, if it does not span multiple rows (if it does, SQLDMO will go get them all).
			case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0))
					then t.id else null end,
         c.iscomputed,
         c.id,
				-- Not For Replication
			convert(bit, ColumnProperty(@objid, c.name, N'IsIdNotForRepl')),
         convert(bit, ColumnProperty(@objid, c.name, N'IsFulltextIndexed')),
         convert(bit, ColumnProperty(@objid, c.name, N'UsesAnsiTrim')),
				-- Non-DRI Default owner and name
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else schema_name(sysod.schema_id) end,
			case when (c.cdefault = 0) then null when (OBJECTPROPERTY(c.cdefault, N'IsDefaultCnst') <> 0) then null else d.name end,
				-- Non-DRI Rule owner and name
			case when (c.domain = 0) then null else schema_name(sysor.schema_id) end,
			case when (c.domain = 0) then null else r.name end,
           -- column level collation
         c.collation,
           -- IsIndexable
         ColumnProperty(@objid, c.name, N'IsIndexable'),
         c.language
		from dbo.syscolumns c
				-- NonDRI Default and Rule filters
			left outer join (dbo.sysobjects d join sys.all_objects sysod on d.id = sysod.object_id)  on d.id = c.cdefault
			left outer join (dbo.sysobjects r join sys.all_objects sysor on r.id = sysor.object_id)  on r.id = c.domain
				-- Fully derived data type name
			join dbo.systypes st on st.xusertype = c.xusertype
				-- Physical base data type name
			join dbo.systypes bt on bt.xusertype = c.xtype
				-- DRIDefault text, if it's only one row.
			left outer join dbo.syscomments t on t.id = c.cdefault and t.colid = 1
					and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2)
		where c.id = @objid
		order by c.colid

	/* Convert any timestamp column to binary(8) if they asked. */
	if (@flags & 0x80000 != 0)
		update #sphelpcols set col_typename = N'binary', col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N'timestamp'

	/* Now see what our flags are, if anything. */
	if (@flags is not null and @flags != 0)
	begin
		if (@flags & 0x0400 != 0)
		begin
			/* Track from xusertype --> b.xtype --> u.xusertype in systypes */
			/* First mask off the things we will set.  The convert() awkwardness is */
			/* necessitated by SQLServer's handling of 0x-prefixed values. */
			declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002))
			update #sphelpcols set col_typename = b.name,
				-- ReInitialize flags to whether it's a length-specifiable type, or a numeric type, or 0.
				col_flags = col_flags & ~@typeflagmask
							+ case when b.name in (N'char',N'varchar',N'binary',N'varbinary',N'nchar',N'nvarchar') then 0x0001
								when b.name in (N'decimal',N'numeric') then 0x0002
								else 0 end
			from #sphelpcols c, dbo.systypes n, dbo.systypes b
				where n.name = col_typename				--// xtype (base type) of name
					and b.xusertype = n.xtype			--// Map it back to where it's xusertype, to get the name
		end
	end

	/* Determine if the column is in the primary key */
	if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N'TableHasPrimaryKey') <> 0)) begin
		declare @indid int
		select @indid = indid from dbo.sysindexes i where i.id = @objid and i.status & 0x0800 <> 0
		if (@indid is not null)
			update #sphelpcols set col_flags = col_flags | 0x0004
			from #sphelpcols c, dbo.sysindexkeys i
				where i.id = @objid and i.indid = @indid and i.colid = c.col_id
	end

	/* OK, now put out the data.  @flags2 added for DaVinci; currently only bit 1 (sp_help filtering of prec/scale) is relevant. */
	set nocount off
	if (@orderby is null or @orderby = N'id')
	begin
		select c.col_name, c.col_id, c.col_typename, c.col_len,
					-- Prec/scale only for numeric/decimal
				col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_prec else NULL end,
				col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_scale else NULL end,
				col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
			   c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName,
            collation = c.col_collation,
            ColType = case when( col_basetypename in (N'image')) then d.FT_COLNAME else NULL end,   /* FullText column name for image column */
            case when ( c.col_isindexable is null ) then 0 else c.col_isindexable end,
            case when ( c.col_language >= 0 ) then c.col_language else -1 end
		from ((#sphelpcols c
      left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid)
      left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where
                       scol.colid = sdep.depnumber and
                       sdep.deptype = 1 and
                       scol.id = @objid and
                       sdep.depid = @objid and
                       ColumnProperty(scol.id, scol.name, N'IsTypeForFullTextBlob') = 1) as d on c.col_id = d.FT_ID
		order by c.col_id
	end else begin
		select c.col_name, c.col_id, c.col_typename, c.col_len,
					-- Prec/scale only for numeric/decimal
				col_prec = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_prec else NULL end,
				col_scale = case when (col_basetypename in (N'decimal',N'numeric') or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0))
						then c.col_scale else NULL end,
				col_basetypename, c.col_defname, c.col_rulname, c.col_null, c.col_identity, c.col_flags,
			   c.col_seed,
            c.col_increment, c.col_dridefname, cn.text, c.col_iscomputed, cm.text, c.col_NotForRepl,
            c.col_fulltext, c.col_AnsiPad, c.col_DOwner, c.col_DName, c.col_ROwner, c.col_RName,
            collation = c.col_collation,
            ColType = case when( col_basetypename in (N'image')) then d.FT_COLNAME else NULL end,   /* FullText column name for image column */
            case when ( c.col_isindexable is null ) then 0 else c.col_isindexable end,
            case when ( c.col_language >= 0 ) then c.col_language else -1 end
		from ((#sphelpcols c
      left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid)
      left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where
                       scol.colid = sdep.depnumber and
                       sdep.deptype = 1 and
                       scol.id = @objid and
                       sdep.depid = @objid and
                       ColumnProperty(sdep.id, scol.name, N'IsTypeForFullTextBlob') = 1) as d on c.col_id = d.FT_ID
		order by c.col_name
	end


 
Last revision 2008RTM
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