Get-Collomns (sqlserver)

Sep 5, 2010 at 12:53 PM

1. I think to be acurate it must be

       columnproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'isidentity') AS IS_IDENTITY,

instead of

       columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'isidentity') AS IS_IDENTITY,

(TABLE_CATALOG is not required, as the query is invoke in the context of a specific database aka TABLE_CATALOG).

It might be bad practice to have tables not owned by dbo, but what isn't forbidden, will be done by someone and than you see the difference.

2. In INFORMATION_SCHEMA.COLUMNS, I miss a criteria whether a column belongs to a table or a view

       (select TYPE from sysobjects where id = OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME ))

the table sql_columns has to extended by table_type  varchar(2) NULL  -- the excpected values are 'U' for table and 'V' for view.

Is there another way to get this information? The use of sysobjects smells a bit depricated.