CREATE procedure sp_columnpos

/*
** Title:       Column Position
** Version:	2.0.1
** Date:        14 IX 1999
** Author:      Jarlath O'Grady MCP
** SQL Version: 6.5, 7.0
** Description: Returns the position of the column(s) within a table
** Parameters:	@tab	Name of table
**		@col	Name of column within table
** Comments:	With no column parameter position of all columns is displayed.
**		Includes error handling for non-existing tables or columns
*/

	@tab	varchar (30)	= null,
	@col	varchar (30)	= null, 
	@num	tinyint 	= 0 output
AS
	
declare	@print	varchar (255),
		@ver	decimal (18, 5)


select	@ver = convert (decimal (18,5), substring (@@version, 21, 6))


if	@tab is null
	begin
	print	''
	print	'Execution failed because the table name, Parameter @tab is required.'
	print	''

	select	@print = 'Please use one of the following tables:'

	print	@print
	print	''


	set	nocount on
	select	convert (varchar, lower(sysobjects.name)) as 'Table', count (syscolumns.id) as 'Columns'
	from	sysobjects, syscolumns
	where	sysobjects.type in ('s', 'u')
	and	syscolumns.id = object_id(sysobjects.name)
	group by sysobjects.name
	set	nocount off

	return
	end

select	@num = count(1)
from	sysobjects

where	type in ('s', 'u')
and	name = @tab

if	@num = 0
	begin
	print	''
	select	@print = 'The table, '+@tab+' does not exist. Execution terminated.'

	print	@print 
	print	''
	return
	end


if	@col is null
	begin

	-- Determine number of columns in selected table
	set	nocount on
	select	@num = count(1)
	from	syscolumns
	where	id = object_id(@tab)
	set	nocount off

	print	''
	select	@print = 'Parameter @col not specified therefore position for all '+convert (varchar, @num)+' columns from table '+@tab+':'
	print	@print
	print	''

	set	nocount on
	print	'Order by Name'
	print	''
	select	convert (varchar, lower(name)) as 'Table Name', colid as 'Position'
	from	syscolumns

	where	id = object_id(@tab)
	order by name

	print	''

	print	'Order by Position'
	print	''
	select	colid as 'Position', convert (varchar, lower(name))as 'Table Name'
	from	syscolumns
	where	id = object_id(@tab)
	order by colid
	set	nocount off

	return
	end

select	@num = count(1)

from	syscolumns
where	object_name (id) = @tab
and	name = @col

if	@num = 0
	begin
	print	''
	select	@print = 'The column, '+@col+', does not exist in table '+@tab+'. Execution terminated.'
	print	@print 
	print	''
	return
	end


select	@num = colid
from	syscolumns
where	object_name (id) = @tab
and	name = @col

select	@num