SQL Programming
Unexpected result in a metadata query in SQL service
Gravatar is a globally recognized avatar based on your email address. Unexpected result in a metadata query in SQL service
  Alejandro A Sosa
  All
  Jul 8, 2021 @ 06:25pm

Hi,

I create a data dictionary with information about the tables and fields in a SQL server database with the following commands:

TEXT TO lcSql TEXTMERGE  PRETEXT 1 + 2 + 8
	SELECT T.NAME AS [SQL_TABLE], C.NAME AS [SQL_FIELD], P.NAME AS [SQL_TYPE], P.MAX_LENGTH AS[SQL_LEN], CAST(P.PRECISION AS INT) AS [SQL_DEC]
	  FROM <<This.cDatabase>>.SYS.OBJECTS AS T
	  JOIN <<This.cDatabase>>.SYS.COLUMNS AS C
	    ON T.OBJECT_ID=C.OBJECT_ID
	  JOIN <<This.cDatabase>>.SYS.TYPES AS P
	    ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
	 WHERE T.TYPE_DESC='USER_TABLE'
ENDTEXT
lnResult = SQLEXEC(This.nHandle,lcSQL,'Temp')

Some of the tables in the database have fields called "Name", and "Description", and in the resulting query those two names came up twice, once with the field type that it has in the table, and another time with of the field type of "sysname". I presume these refer to the name and description of the column, not necessarily the columns that will hold my data.

Why does this happen?

TIA,

Alex

Gravatar is a globally recognized avatar based on your email address. *** Solved *** re: Unexpected result in a metadata query in SQL service
  Alejandro A Sosa
  Alejandro A Sosa
  Jul 10, 2021 @ 04:48am

Solved:

"Sysnames" are data that SQL Server uses for some of its own work.

Help suggested to ignore them so I just filtered out those records.

Alex

© 1996-2021