SQLSERVER系统表应用之基于Table生成存储过程参数列表

       有时一个Table有很多列，你需要写一个存储过程，那个Table的参数列表够你写的了。我们可以利用系统表生成这个列表，看T-SQL:

select '@' + c.name, col_definition =
case t.name
when 'bigint' then 'bigint,'
when 'int' then 'int,'
when 'smallint' then 'smallint,'
when 'tinyint' then 'tinyint,'
when 'bit' then 'bit,'
when 'decimal' then 'decimal,'
when 'numeric' then 'numeric,'
when 'money' then 'money,'
when 'smallmoney' then 'smallmoney,'
when 'float' then 'float,'
when 'real' then 'real,'
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
when 'sql_variant' then 'sql_variant,'
when 'timestamp' then 'timestamp,'
when 'uniqueidentifier' then 'uniqueidentifier,'
when 'xml' then 'xml,'
else t.name + '(' + cast(c.prec as varchar(50)) + '),'
end
from sysobjects s
inner join sys.syscolumns c on s.id = c.id
inner join sys.types t on t.user_type_id = c.xtype
where s.xtype='U'
and s.name = 'Product'

结果是：

(No column name)	col_definition
@ProductID	int,
@Name	nvarchar(50),
@ProductNumber	nvarchar(25),
@MakeFlag	bit,
@FinishedGoodsFlag	bit,
@Color	nvarchar(15),
@SafetyStockLevel	smallint,
@ReorderPoint	smallint,
@StandardCost	money,
@ListPrice	money,
@Size	nvarchar(5),
@SizeUnitMeasureCode	nchar(3),
@WeightUnitMeasureCode	nchar(3),
@Weight	decimal,
@DaysToManufacture	int,
@ProductLine	nchar(2),
@Class	nchar(2),
@Style	nchar(2),
@ProductSubcategoryID	int,
@ProductModelID	int,
@SellStartDate	datetime,
@SellEndDate	datetime,
@DiscontinuedDate	datetime,
@rowguid	uniqueidentifier,
@ModifiedDate	datetime,

 

这里我使用的Table来自AdventureWorks，完了，这么简单。

Author:  Petter Liu   http://wintersun.cnblogs.com

希望这篇Post对您有帮助。