在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
增加表中字段
CREATE PROCEDURE dbo.AddUserTableField
@TableName sysname,
@FieldName varchar(200)
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @TableName)
BEGIN
exec ('alter table ' + @TableName + ' Add ' + @FieldName +' varchar(200) ')
END
删除表中字段
CREATE PROCEDURE dbo.DropUserTableField
@TableName sysname,
@FieldName varchar(200)
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @TableName)
BEGIN
exec ('alter table ' + @TableName + ' drop column ' + @FieldName )
END
数据库重命名(这个貌似会出错，出于共享锁的原因)
create procedure RenameDataBase
@OldDataBaseName varchar(20),
@NewDataBaseName varchar(20)
as
begin
EXEC sp_renamedb @OldDataBaseName ,@NewDataBaseName
end
获取所有的存储过程
create procedure SelectAllProcedure
as
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
go
获取所有的视图
create procedure SelectAllView
as
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=2
go
posted @ 2007-12-19 11:09 Judas 阅读(40) 评论(0)
编辑