Would be pleased to receive some SQLServer 6.5 data dictionary queries for various types of objects (tables, indexes, foreign keys, etc.). Also, any example of how to "decode" (in Oracle parlance) the syscolumns.status field to meaningful strings in a report.

-- Anonymous, September 09, 1997

Answers

Re: Data Dictionary queries and reports; tinyint decode

Kirk,

We use ERwin here.  But as far as stuff we have ginned up ourselves, 
my colleague Terry Cummings has created a view called tables_and_col-
ums as shown below.

	CREATE VIEW tables_and_columns 
	AS
	select 
		table_name=o.name,
		column_seq=c.colid,
		column_name=c.name,
		column_type=t.name,
		column_length=c.length
	from
		sysobjects o,
		syscolumns c,
		systypes t
	where 
		o.type = 'U' and
		o.id  = c.id and


		c.usertype = t.usertype

Terry can attach to this view in Access and make reports.

In answer to your second question, I found some information 
about the syscolumns table and its status column.  I did not find 
descriptions of the 1, 2, and 4 bits, but the other bits are 
described.  I think the missing bit descriptions will not be a
problem; the 1-bit and 2-bit are used by a couple of system 
tables.  The 4-bit does not seem to be used.

You can then write some SQL to describe the status bits, like so:

select "Table Name" = b.name,
       "Column Name" = a.name,
       "Status Description" = 
       case
          when (status & 128) > 0 then 'Identity ' else null
       end
       +
       case
          when (status & 64) > 0 then 'Output ' else null
       end
       +
       case
          when (status & 32) > 0 then 'Varchar/Varbinary ' else null
       end
       +
       case
          when (status & 16) > 0 then 'ANSI Padding ' else null
       end
       +
       case
          when (status & 8) > 0 then 'Nullable ' else null
       end
       +
       case
          when (status & 4) > 0 then '4-bit ' else null
       end
       +
       case
          when (status & 2) > 0 then '2-bit ' else null
       end
       +
       case
          when (status & 1) > 0 then '1-bit' else null 
       end
       + ""
 from syscolumns a,
      sysobjects b
 where a.id = b.id

The information I found about the status column in the syscolumns
table is attached below.

			Hope this helps,

			Eric Kohnen
			Client Registry Database Manager
			Washington State Dept. of Social & Health Services,
			Research & Data Analysis
			kohnen@scn.org
			(360) 902-0732



For SQL Server 6.5 information, see syscolumns System 
Table in What's New for SQL Server 6.5.

Contains one row for every column in every table and 
view, and a row for each parameter in a stored procedure.
		
		
Column	 Datatype	Description
		
id	 int		ID of the table to which this column 
			belongs or of the stored procedure with 
			which this parameter is associated.
number	 smallint	Subprocedure number when the procedure 
			is grouped (0 for nonprocedure entries).
colid	 tinyint	Column ID.
status	 tinyint	Bitmap used to describe a property of 
			the column or the parameter:
			0x08   The column allows null values.
			0x40   The parameter is an OUTPUT parameter.
			0x80   The column is an identity column.
type	 tinyint	Physical storage type; copied from systypes.
length	 tinyint	Physical length of data; copied from systypes 
			or supplied by the user.
offset	 smallint	Offset into the row where this column appears; 
			if negative, variable-length column.
usertype smallint	User type ID; copied from systypes.
cdefault int		ID of the stored procedure that generates the 
			default value for this column.
domain	 int		ID of the stored procedure that contains the 
			rule for this column.
name	 varchar(30)	Column name.
printfmt varchar(255)	Reserved.
prec	 tinyint	Level of precision for this column.
scale 	 tinyint	Scale for this column. 

Index
syscolumns clustered, unique on id, number, colid
Referenced by Stored Procedures
		
		
sp_articlecolumn	sp_help			sp_special_columns
sp_bindefault		sp_helparticle		sp_sproc_columns
sp_bindrule		sp_helparticlecolumns	sp_statistics
sp_column_privileges	sp_helprotect		sp_tables
sp_columns		sp_pkeys		sp_unbindefault
sp_droptype		sp_rename		sp_unbindrule
sp_fkeys		

  

In SQL Server 6.5, two status bits have been added to the 
status column in syscolumns. 
	
	
New status bits	Description
16	Indicates ANSI_PADDING was in affect when a varchar
        or a varbinary column was created. Indicates that 
        trailing blanks are preserved when varchar data is 
        updated or inserted; and that trailing zeroes are 
        preserved when varbinary data is updated or inserted.
32	Indicates that a varchar or a varbinary column is a 
        fixed-length datatype that accepts NULLS. varchar 
        data is padded with blanks to the maximum length and 
        varbinary data is padded with zeroes.
<\PRE>



-- Anonymous, September 11, 1997

Your tinyint decode, etc. response

Thanks for the very good reply. I'm new to SQLServer, but if you ever have an Oracle question, I'd be happy to entertain it. For all, http://www.swynk.com is a good site. --Regards, Kirk :-)

-- Anonymous, September 11, 1997

Thanks, and a follow-up

Came upon this list by accident while browsing the Greenspun site. Wondered if I may ask a follow-up.

The note says that this View could be tried in Access. I'd love to do so but couldn't find out how. I'll admit I'm new to Access (95), but I saw no easy way to enter this query. I realize this is a SQL Server site, so perhaps the "Access" referred to is something different.

Any help appreciated. Pardon the naivete.

-- Anonymous, October 04, 1997

Views in Access

Yes, it was referring to Access, another Microsoft database product. Per the Help file for Access:

You can use SQL, or Structured Query Language, to query, update, and manage relational databases such as Microsoft Access. When you create a query in query Design view, behind the scenes Microsoft Access constructs the equivalent SQL statements. You can view or edit the SQL statement in SQL view . After you make changes to a query in SQL view, the query might not be displayed the way it was previously in query Design view.

Some queries can't be created in the design grid. For pass-through, data-definition, and union queries, you must create SQL statements directly in SQL view. You can also use SQL statements in many places in Microsoft Access where you can enter the name of a table, query, or field. In some cases, Microsoft Access fills in the SQL statement for you. For example, when you use a wizard to create a form or report that gets data from more than one table, Microsoft Access automatically creates an SQL statement that it uses as the setting for the RecordSource property of the form or report. When you create a list box or combo box with a wizard, Microsoft Access creates the SQL statement and uses it as the setting for the RowSource property of the list box or combo box. You can also use SQL statements in subqueries in the query design grid, in the SQL Statement argument of the RunSQL macro action, and in code.

-- Anonymous, October 06, 1997