Query to get all user tables with columns

[Dag]

Hi,

I tried to create a simple view as follows

CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE

FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP

WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)

Combined with consistent naming conventions I will use this view to
easily find foreign keys; a la

SELECT *
FROM V_ALL_USERTABLE_COLUMNS
WHERE ColName LIKE ('%user_id')
There is something wrong with my view definition that I don't get
though; it doesn't return all the columns. I have a table with the
following definition

CREATE TABLE [dbo].[c_messages]
(
[cid] [int] IDENTITY (1, 1) NOT NULL ,
[touser_id] [int] NULL ,
[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fromuser_id] [int] NOT NULL ,
[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[message_read] [bit] NOT NULL ,
[logout] [bit] NULL
) ON [PRIMARY]
GO
The problem is that the select I used to define the view doesn't
return the touser_id column. I have sort of a sneaking suspicion that
the problem has to do with joining syscolumns.type to systypes.type,
but I don't know what to do instead (I'd really like to include the
type; it's useful if I ever changed the type of a primary key and want
to check that I also changed all the foreign keys).

Any help would be appreciated!

[David Portas]

Use the information schema rather than the system tables:

SELECT * FROM information_schema.columns

This format is much easier to use.

Your original query should work if you join on XTYPE rather than TYPE
but this is not recommended. In general you should avoid referencing
system tables directly.

--
David Portas
SQL Server MVP
--