SQL server 表操作语句(原创)
CREATE TABLE [dbo].[test] (
[id11] int NOT NULL ,
[as] varchar(1) COLLATE Chinese_PRC_CI_AS NULL ,
[asd] varchar(1) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK__test__3213E83F753864A1] PRIMARY KEY ([id11])
)
ON [PRIMARY]
GO
--删除表
IF EXISTS(select * from sysobjects where id=object_id('[test]') and objectproperty(id,'IsTable')=1)
drop TABLE test1111
ALTER TABLE [dbo].[test] DROP CONSTRAINT [PK__test__3213E83F753864A1]
GO
ALTER TABLE [dbo].[test] ALTER COLUMN [asdad] varchar(1) COLLATE Chinese_PRC_CI_AS NOT NULL
GO
--更名
EXEC sp_rename N'[dbo].[test].[asdad]', N'id11', 'COLUMN'
GO
--是否空
ALTER TABLE [dbo].[test] ALTER COLUMN [id11] int NOT NULL
GO
--主键
ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK__test__3213E83F753864A1] PRIMARY KEY ([id11])
GO
alter table test drop constraint id11
--单主键改双主键
ALTER TABLE [dbo].[test] DROP CONSTRAINT [PK__test__3213E83F753864A1]
GO
ALTER TABLE [dbo].[test] ADD CONSTRAINT [PK__test__3213E83F753864A1] PRIMARY KEY ([id11], [asas])
GO
--字段类型
ALTER TABLE [dbo].[test] ALTER COLUMN [asas] tinyint
GO
--索引
CREATE INDEX [qw] ON [dbo].[test]
([id11] ASC)
GO
DROP INDEX [qw] ON [dbo].[test]
GO
--外键
ALTER TABLE [dbo].[test] ADD CONSTRAINT [we] FOREIGN KEY ([id11]) REFERENCES [dbo].[dt_article] ([id]) ON DELETE NO ACTION
GO
ALTER TABLE [dbo].[test] DROP CONSTRAINT [we]
GO
CREATE TRIGGER [dbo].[we]
ON [dbo].[test]
AFTER
AS
BEGIN
-- type your statements here
END
GO
--唯一键
ALTER TABLE [dbo].[test] ADD CONSTRAINT [wsas] UNIQUE ([id11])
GO
--检查
ALTER TABLE [dbo].[test] ADD CONSTRAINT [we] CHECK (we)
GO
--触发器 注释
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'test',
NULL, NULL)) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'asasa'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'test'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'asasa'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'test'
GO
use demodtcms --等同 demodtcms..dt_express
go
--查询表的主键名
DECLARE @PKName VARCHAR(100)
select @PKName=name from sysobjects where parent_obj in (
select id from sysobjects where name='gggggggggg')
and xtype='pk'
PRINT @PKName
if(@PKName <> '')
EXEC('ALTER TABLE [dbo].[gggggggggg] DROP CONSTRAINT ['+ @PKName +']')
GO
--查询表的默认值名
DECLARE @DFName VARCHAR(100)
select @DFName=name from sysobjects where parent_obj in (
select id from sysobjects where name='gggggggggg')
and xtype='D'and id in(SELECT e.id from syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name ='gggggggggg' left join syscomments e on a.cdefault=e.id WHERE a.name='asda11')
PRINT @DFName
if(@DFName <> '')
EXEC('ALTER TABLE [dbo].[gggggggggg] DROP CONSTRAINT ['+ @DFName +']')
GO
最新文章
- JSON学习之一
- create mystic by Django
- 你早该这么玩Excel 读书笔记
- 清北学堂入学测试P4751 H’s problem(h)
- win10大水牛主机插入耳机没有声音
- sql修改一个字段多个值
- Oracle 11g R2性能优化 10046 event
- C语言--第2次作业
- QML用Qt.labs.settings实现保存用户设置
- window下tomcat的内存溢出问题
- 集腋成裘-02-css基础-01
- OPENJDK 源码编译
- DevOps平台的“精益创业”之路
- Final互评------《弹球学成语》---- 杨老师粉丝群
- bootstrap中给表格设置display之后表格宽度变小问题解决
- 报表使用hive数据源报java.net.SocketTimeoutException: Read timed out
- sublim3常用插件安装
- [ci] jenkins的Timestamper插件-让日志显示时间
- iOS APNs实战分享
- 十大基于Docker的开发工具