>I need a stored proc to enumerate tables and then one to enumerate fields. This is straight from the the boss. I know how to use SQLDMO to get this but I have no experiance with stored procs. :( When it rains it pours.
>
>Joe.

I have made a very tight SP here. The trick was to get a grip on the Sysobjects object. From there you could do far more than this!

/* JAJ 
Input: TableName OR null.
IF param IS null THEN lists ALL the tables 
Otherwise return all the fields of the of the table passed
*/
CREATE PROC [dbo].[sp_Get_Tables]
	@qxTablename varchar(50)=NULL
As
	DECLARE @TableName varchar(40),@ColumnName nvarchar(30),@Type nvarchar(20),@Nullable nvarchar(5)
	DECLARE @strLength nvarchar(10)
	DECLARE @objid int ,@Length int
	DECLARE @SNoTable tinyint
	DECLARE @SNo tinyint
	DECLARE @strSNo AS nvarchar(5)

IF @qxTablename IS NOT NULL  /* If there is no table passed */
    begin
    	SELECT Name AS 'Column_Name',type_name(xusertype) as 'Type', convert(int, length) as 'Length',
    		CASE WHEN isnullable = 0 THEN 'no' ELSE 'yes' END as 'Nullable'
    		FROM syscolumns WHERE id =object_id (@qxTablename) AND number = 0
end

IF @qxTablename IS NULL
	Begin
		SELECT Name as TableName FROM Sysobjects where xtype ="U"
	End
/* BANG */
GO