sp_columns1,sp_columns2

/**
查询表格的列,列类型,列长度
*/
CREATE PROC sp_columns1
  @table_name VARCHAR(1000)
AS
BEGIN
  SELECT a.name, c.data_type, a.length
  FROM   syscolumns a LEFT JOIN information_schema.columns c ON a.name = c.column_name AND c.TABLE_NAME = @table_name
  WHERE  a.ID = OBJECT_ID(@table_name)
END

/**
查询表的所有列,横向显示
*/
CREATE PROC sp_columns2(@table_name VARCHAR(1000))
AS
BEGIN
  DECLARE @column_name VARCHAR(100) --列名
  DECLARE @columns VARCHAR(1000) --所有列名

  SELECT @columns = ''

  DECLARE
    mycursor CURSOR FOR
      SELECT name
      FROM   syscolumns
      WHERE  id = (SELECT id
                   FROM   sysobjects
                   WHERE  name = @table_name)

  OPEN mycursor
  FETCH NEXT FROM MyCursor INTO @column_name

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @columns = @columns + @column_name + ','
    FETCH NEXT FROM MyCursor INTO @column_name
  END

  CLOSE MyCursor

  DEALLOCATE MyCursor

  SELECT @columns
END