SQL server 表動作陳述式（原創）

標籤：des   io   ar   sp   for   on   art   cti   bs   

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

 

SQL server 表動作陳述式（原創）