存储过程实现对MS SQL表结构的操作-技术开发专区

存储过程实现对MS SQL表结构的操作

作者：ITPUB论坛编辑： nancy 2008-05-23 00:00

【IT168技术文档】

在itpub中看到一个c# Winform实现对数据库的操作，可惜很多是写死了的，不过这到让我想到了是否可以通过存储过程实现对数据库和表结构的操作。有些时候，项目中可能要动态的增加表字段，删除表字段，重命名之类的，而客户端不一定能访问到远程的服务器，在这个时候也许就会有些许用处了。总结了一下，我把相关的存储过程实现，经在SQL2005 测试是OK的，我把他放上来，跟大家分享，同时欢迎大家多多指教.
希望能对大家有用.

创建表

CREATE PROCEDURE dbo.CreateUserTable
@TableName sysname
AS
EXEC('CREATE TABLE '+@TableName+
' (column1 varchar(100), column2 varchar(100))');
GO

读取表中字段

create procedure SelectTableField
@TableName varchar(50)
as
SELECT     TABLE_CATALOG AS [Database], TABLE_SCHEMA AS Owner, TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName,  ORDINAL_POSITION AS OrdinalPosition, COLUMN_DEFAULT AS DefaultSetting, IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,  CHARACTER_MAXIMUM_LENGTH AS MaxLength, DATETIME_PRECISION AS DatePrecision,COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IsIdentity
FROM         INFORMATION_SCHEMA.COLUMNS WHERE     (TABLE_NAME = @TableName)
删除表
CREATE PROCEDURE dbo.DropUserTable
@TableName sysname
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @TableName)
BEGIN
EXEC('drop TABLE ' +  @TableName);
END
表的重命名
CREATE PROCEDURE dbo.ReNameUserTable
@OldTableName sysname,
@NewTableName sysname
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @OldTableName)
BEGIN
exec sp_rename  @OldTableName ,@NewTableName
END

关注我们