循环当前数据库中所有用户建的表代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】循环当前数据库中所有用户建的表
作者:Dezai.CN / 发布于2011/10/26/ 836
<div>
<p align="left"><span>CREATE</span><span> <span>PROCEDURE</span> sp_AlterColumnType</span>
<p align="left"><span>AS</span>
<p align="left"><span>-- =============================================</span>
<p align="left"><span>-- </span><span>循环当前数据库中所有用户建的表</span>
<p align="left"><span>-- =============================================</span>
<p align="left"><span>DECLARE</span><span> @TableName<span> </span><span>nvarchar</span><span>(</span>100<span>)</span></span>
<p align="left"><span>DECLARE</span><span> @TableID<span> </span><span>nvarchar</span><span>(</span>100<span>)</span></span>
<p align="left">
<p align="left"><span>DECLARE</span><span> cursor_CustomTable <span>CURSOR</span> <span>FOR</span></span>
<p align="left"><span>SELECT</span><span> [name]<span>,</span>[ID] <span>FROM</span> sysobjects <span>where</span> <span>type</span> <span>=</span><span>'U'</span></span>
<p align="left">
<p align="left"><span>OPEN</span><span> cursor_CustomTable</span>
<p align="left">
<p align="left"><span>FETCH</span><span> NEXT <span>FROM</span> cursor_CustomTable <span>INTO</span> @TableName<span>,</span>@TableID</span>
<p align="left">
<p align="left"><span>WHILE</span><span> <span>@@FETCH_STATUS</span> <span>=</span> 0</span>
<p align="left"><span>BEGIN</span>
<p align="left"><span><span>-- =============================================</span></span>
<p align="left"><span><span>-- </span></span><span>循环当前表中所有列</span><span>,</span><span>取出列名和列的类型</span>
<p align="left"><span><span>-- =============================================</span></span>
<p align="left"><span><span>DECLARE</span> @ColumnName <span>nvarchar</span><span>(</span>100<span>)</span></span>
<p align="left"><span><span>DECLARE</span> @ColumnType <span>nvarchar</span><span>(</span>100<span>)</span></span>
<p align="left">
<p align="left"><span><span>DECLARE</span> cursor_Column <span>CURSOR</span> <span>FOR</span></span>
<p align="left"><span><span>select</span> [name]<span>,</span>xtype <span>from</span> syscolumns <span>where</span> [id] <span>=</span> @TableID <span>and</span> xtype<span>=(</span><span>select</span> xtype <span>from</span> systypes <span>where</span> <span>name</span><span>=</span><span>'numeric'</span><span>)</span></span>
<p align="left">
<p align="left"><span><span>OPEN</span> cursor_Column</span>
<p align="left"><span><span>FETCH</span> NEXT <span>FROM</span> cursor_Column <span>INTO</span> @ColumnName<span>,</span>@ColumnType</span>
<p align="left">
<p align="left"><span><span>WHILE</span> <span>@@FETCH_STATUS</span> <span>=</span> 0</span>
<p align="left"><span><span>BEGIN</span></span>
<p align="left"><span><span>--</span></span><span>在此处修改列类型为</span><span>numeric(14,2)</span>
<p align="left"><span><span>exec</span><span>(</span><span>'ALTER TABLE '</span><span>+</span>@TableName <span>+</span><span>' ALTER COLUMN '</span><span>+</span>@ColumnName <span>+</span><span>' numeric(14,2) null'</span><span>)</span></span>
<p align="left">
<p align="left"><span><span>FETCH</span> NEXT <span>FROM</span> cursor_Column <span>INTO</span> @ColumnName<span>,</span>@ColumnType</span>
<p align="left">
<p align="left"><span><span>END</span></span>
<p align="left">
<p align="left"><span><span>CLOSE</span> cursor_Column</span>
<p align="left"><span><span>DEALLOCATE</span> cursor_Column</span>
<p align="left">
<p align="left"><span>-- </span><span>循环到下一个表</span><span>.</span>
<p align="left"><span>FETCH</span><span> NEXT <span>FROM</span> cursor_CustomTable <span>INTO</span> @TableName<span>,</span>@TableID</span>
<p align="left"><span>END</span>
<p align="left"><span>CLOSE</span><span> cursor_CustomTable</span>
<p align="left"><span>DEALLOCATE</span><span> cursor_CustomTable</span>
<p align="left"><span>GO</span>
<p align="left"><span>
</span>
</div>
试试其它关键字
　用户建的表　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)