Data Dictionary queries and reports; tinyint decodegreenspun.com : LUSENET : SQL Server Database Administration : One Thread
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
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 firstname.lastname@example.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
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
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
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