Getting columns for sys. tables

Question

Post reply

Getting columns for sys. tables

tnpich

Ten Centuries

Points: 1223

August 10, 2020 at 9:46 pm

#3778781

I have a data dictionary project. I can easily get the data tables and their columns, as well as Views.

For Views I can get the table and column if its a user table. However some Views are based on system tables. i.e.: sys.columns, sys.objects which do not come out in the table query, and which I would not normally want to include in the data dictionary.

When I do have to get those tables/columns, how would I get them?

The query to get the tables (and other main objects) is here

;WITH MainObjects AS
(
SELECT DISTINCT
ISC.Table_CatalogAS TCatalog,
ISC.Table_SchemaAS TSchema,
SAO.parent_object_idAS ParentId,
SAO.object_idAS ObjectId,
SAO.nameAS Name,
SAO.type_descAS TypeDesc,
SAO.create_dateAS CreateDate,
SAO.modify_dateAS ModifyDate,
SAO.is_ms_shippedAS Shipped
FROM [?].sys.all_objects SAO 
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNSISCONSAO.name =  ISC.table_name 
WHERESAO.is_ms_shipped = 0AND
(SAO.type = ''FN''OR
 SAO.type = ''P''OR
 SAO.type = ''U''OR
 SAO.type = ''V'')
)
INSERT INTO #MainObjects 
SELECT 
''?'',
TCatalog,
TSchema,
ParentId,
ObjectId,
Name,
TypeDesc,
CreateDate,
ModifyDate,
Shipped
FROM MainObjects

I then use this to pick up the columns

EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
;WITH ColumnObjects AS
(
SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE Table_Name NOT LIKE ''sysdia%''
)
INSERT INTO #ColumnObjects
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEFAULT,
CASE IS_NULLABLE WHEN ''Yes'' THEN 1 ELSE 0 END,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION
FROM ColumnObjects
END'

I've tried playing around with both extract routines but can't get the columns for the sys. tables.

Any suggestions?

TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24699 More actions · Answer 1

If you look at the code behind the view INFORMATION_SCHEMA.COLUMNS, it is looking at USER objects only:

CREATE VIEW INFORMATION_SCHEMA.COLUMNS
AS
SELECT
DB_NAME()AS TABLE_CATALOG,
SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA,
o.nameAS TABLE_NAME,
c.nameAS COLUMN_NAME,
COLUMNPROPERTY(c.object_id, c.name, 'ordinal')AS ORDINAL_POSITION,
convert(nvarchar(4000),
OBJECT_DEFINITION(c.default_object_id))AS COLUMN_DEFAULT,
convert(varchar(3), CASE c.is_nullable
WHEN 1 THEN 'YES' ELSE 'NO' END)AS IS_NULLABLE,
ISNULL(TYPE_NAME(c.system_type_id), t.name)AS DATA_TYPE,
COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen')AS CHARACTER_MAXIMUM_LENGTH,
COLUMNPROPERTY(c.object_id, c.name, 'octetmaxlen')AS CHARACTER_OCTET_LENGTH,
convert(tinyint, CASE -- int/decimal/numeric/real/float/money
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
END)AS NUMERIC_PRECISION,
convert(smallint, CASE-- int/money/decimal/numeric
WHEN c.system_type_id IN (48, 52, 56, 60, 106, 108, 122, 127) THEN 10
WHEN c.system_type_id IN (59, 62) THEN 2 END)AS NUMERIC_PRECISION_RADIX,-- real/float
convert(int, CASE-- datetime/smalldatetime
WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL
ELSE ODBCSCALE(c.system_type_id, c.scale) END)AS NUMERIC_SCALE,
convert(smallint, CASE -- datetime/smalldatetime
WHEN c.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(c.system_type_id, c.scale) END)AS DATETIME_PRECISION,
convert(sysname, null)AS CHARACTER_SET_CATALOG,
convert(sysname, null) collate catalog_defaultAS CHARACTER_SET_SCHEMA,
convert(sysname, CASE
WHEN c.system_type_id IN (35, 167, 175)-- char/varchar/text
THEN COLLATIONPROPERTY(c.collation_name, 'sqlcharsetname')
WHEN c.system_type_id IN (99, 231, 239)-- nchar/nvarchar/ntext
THEN N'UNICODE'
END)AS CHARACTER_SET_NAME,
convert(sysname, null)AS COLLATION_CATALOG,
convert(sysname, null) collate catalog_defaultAS COLLATION_SCHEMA,
c.collation_nameAS COLLATION_NAME,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN DB_NAME() END)AS DOMAIN_CATALOG,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN SCHEMA_NAME(t.schema_id)
END)AS DOMAIN_SCHEMA,
convert(sysname, CASE WHEN c.user_type_id > 256  
THEN TYPE_NAME(c.user_type_id)
END)AS DOMAIN_NAME
FROM
sys.objects o JOIN sys.columns c ON c.object_id = o.object_id
LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE
o.type IN ('U', 'V')

As an alternative, you could use the above instead of the view in your CTE but remove the WHERE o.type IN ('u','v') and you should get what you are looking for.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

tnpich Ten Centuries Points: 1223 More actions · Answer 2

Hi Brian, thanks for your reply and sorry for my delayed response.

I tried removing the where clause in your response and did not get the tables/columns I was looking for, namely the following:

sys.foreign_key_columns

sys.columns

sys.objects

sys.schemas

Doing some additional digging, I found that these are all System Views and there doesn't appear to be an easy way of seeing how they are created.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 3

For system objects, you won't get the system columns for system objects using INFORMATION_SCHEMA or sys.columns. You need to use the following...

sys.all_objects

sys.all_columns

sys.types (to get the datatypes of the columns)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tnpich Ten Centuries Points: 1223 More actions · Answer 4

Hi Jeff,

The use case I have is creating a data dictionary and need to indicate what tables/columns are used in a View. The column attributes are also listed.

One User View gets is data from the sys.x tables that I previously identified. All referenced tables (Main Objects) and columns (Column Objects) need to be in the data dictionary. I can add the sys.x table into the Main Objects table, and the column name into Column Objects, but I can't get the column attributes. So sys.columns provides a name column and can infer its data type, I can't programmatically state the type or its other attributes.

Thanks