SQL table column information

Example:

EXEC [Northwind]..sp_columns_rowset N'Products', N'dbo', NULL 

Try this procedure as well.

CREATE  PROCEDURE dbo.rp_ScriptTableColumnsList (
 @vcDatabaseName varchar(128),
 @vcTable varchar(128),
 @bFormatted bit,
 @bQuotedNames bit,
 @bColumnListOnly bit,
 @vcOUTSQL varchar(8000) OUTPUT
)
AS
/*
** Procedure
 dbo.rp_ScriptTableColumnsList  


** Description
 Useful utility to script out columns in varying forms for use in calling procedures.
 Can run for any table in any database by creating a dynamic script, and existing script
  in database parameter in use. 

** Parameters 

** Requirements 

** Usage 

DECLARE @RC int
DECLARE @vcDatabaseName varchar(128)
DECLARE @vcTable varchar(128)
DECLARE @bFormatted bit
DECLARE @bQuotedNames bit
DECLARE @bColumnListOnly bit
DECLARE @vcOUTSQL varchar(8000)
SELECT 
 @vcDatabaseName = 'pubs', 
 @vcTable = 'authors', 
 @bFormatted = 1, 
 @bQuotedNames = 1, 
 @bColumnListOnly = 1
EXEC @RC = [master_admin].[dbo].[rp_ScriptTableColumnsList] 
 @vcDatabaseName, @vcTable, @bFormatted, @bQuotedNames, @bColumnListOnly, @vcOUTSQL OUTPUT 
PRINT @vcOUTSQL 

** Changes
  2008-04-21 John Perry Created 

*/
set nocount on 

DECLARE  
 @vcSQL varchar(8000),
 @vcSQLSource varchar(8000),
 @vcFormatting varchar(2),
 @vcSELECT varchar(10) 

IF @bFormatted = 1
 SET @vcFormatting = char(10)+ char(9)
ELSE
 SET @vcFormatting = ''
 
IF @bColumnListOnly = 1
 SET @vcSELECT = CASE
  WHEN @bFormatted = 0 THEN '' ELSE CHAR(9) END
ELSE
 SET @vcSELECT = 'SELECT ' + @vcFormatting 

CREATE TABLE #tmpColumns (
 [name] [sysname] NOT NULL ,
 [id] [int] NOT NULL ,
 [length] [smallint] NOT NULL ,
 [colid] [smallint] NOT NULL ,
 [colorder] [smallint] NOT NULL 
)  

SET @vcSQLSource = 'USE ' + @vcDatabaseName  + '
 INSERT INTO #tmpColumns
 SELECT c.name, c.id, c.length, c.colid, c.colorder
 FROM  dbo.syscolumns c
 JOIN dbo.sysobjects o
 ON o.id = c.id
  WHERE o.name = ''' + @vcTable + '''
 ORDER BY
  colorder' 

-- Add the columns to the local temp table for the database in USE (within query).
EXEC (@vcSQLSource) 

--
-- Create initial list of columns and query from results in temp table.
--
SELECT @vcSQL =
 COALESCE(@vcSQL + ', ' + @vcFormatting , @vcSELECT ) + 
 CASE 
 WHEN @bQuotedNames = 1 THEN
  QUOTENAME(name)
 ELSE
  name
 END
FROM #tmpColumns 

-- Clean up.
DROP TABLE #tmpColumns 

--
-- Complete and format the SQL statement with the TABLE FROM statement, as needed.
--
IF @bColumnListOnly = 0
 SET @vcSQL = @vcSQL + 
  CASE
  WHEN @bFormatted = 0 THEN SPACE(1) ELSE CHAR(10) END
  + 'FROM ' + @vcDatabaseName + '.dbo.' + @vcTable 

IF @bFormatted = 0
 SET @vcSQL = REPLACE(@vcSQL, @vcFormatting, '') 

--
-- Final return of requested SQL string.
--
SET @vcOUTSQL = LTRIM(@vcSQL) 
GO