sp_help 'meter'
-- 我的总结
--更改列的类型
alter table meter
alter column LJBuyPwr numeric (10, 2) NOT NULL
--先添加列,不允许为空时,步骤
--1.update Meter set aa='' where aa is null
--2.添加默认值约束
--添加列
alter table meter add zabc_yggl bit
--添加带有默认值的非空列
alter table addpower add aaa tinyint not null default 1
--删除列
ALTER TABLE meter
DROP COLUMN Birthday
--添加默认值
ALTER TABLE [dbo].[Meter] ADD CONSTRAINT [df_LJBuyPwr] DEFAULT ((0)) FOR [LJBuyPwr]
ALTER TABLE [dbo].[Meter] ADD CONSTRAINT [DF_F63MeterType] DEFAULT ((12)) FOR [F63MeterType]
ALTER TABLE [dbo].[Meter] ADD CONSTRAINT [DF_mconst] DEFAULT ((1)) FOR [mconst]
--添加check 约束
alter table meter
add constraint CK_meter_id check((NOT [meter_id] like '%[^0-9]%'))
CREATE TABLE dbo.IPs(ip varchar(15) NOT NULL PRIMARY KEY)
go
--check约束验证IP合法性
ALTER TABLE dbo.IPs ADD CONSTRAINT CHK_IP_validd CHECK
(
ip LIKE '_%._%._%._%'
AND
ip NOT LIKE '%.%.%.%.%'
AND
ip NOT LIKE '%[^0-9.]%'
AND
ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
AND
ip NOT LIKE '%[3-9][0-9][0-9]%'
AND
ip NOT LIKE '%2[6-9][0-9]%'
AND
ip NOT LIKE '%25[6-9]%'
);
go
--创建索引
USE [HLER_0920]
GO
/****** Object: Index [IX_Meter] Script Date: 09/25/2010 17:12:29 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Meter] ON [dbo].[Meter]
(
[RTU_ID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--查询某个表的所存储过程、触发器等
sp_depends 'meter'
--创建一个触发器
create TRIGGER tigSyncTotalPwr ON meter
AFTER insert
AS
update meter set SyncTotalPwr=meter.Per_power from meter,inserted i where meter.m_id=i.m_id
go
--------------------------------------------------------
select * from sys.objects where type='v'
---创建一个函数，并加该函数的约束--------------------------------------
CREATE function [dbo].[chkIP]
(
@ip [nvarchar](50)
)
returns [bit]
as
begin
declare @result bit
set @result=0
if(
@ip LIKE '_%._%._%._%'
AND
@ip NOT LIKE '%.%.%.%.%'
AND
@ip NOT LIKE '%[^0-9.]%'
AND
@ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
AND
@ip NOT LIKE '%[3-9][0-9][0-9]%'
AND
@ip NOT LIKE '%2[6-9][0-9]%'
AND
@ip NOT LIKE '%25[6-9]%'
)
begin
set @result=1
end
return @result
end
GO
alter table NetSets add constraint CK_ProxyIP check ((dbo.chkIP(ProxyIP)=1))
----------------------------------------------------------
--更改架构
alter schema GBTerminal transfer dbo.f27
--查询中间的
select top 20 * from Meter except
select top 10 * from Meter
------复制命令------------------------------------------------------------------
--copy \\tsclient\F\ZaxisSetup.rar e:\
-----------------------------------------------------------------------------
--启用一个login:jy
ALTER LOGIN [jy] enable
--为一个Login名为jy建一个用户jyuser
CREATE USER jyuser FOR LOGIN jy WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember N'db_owner', N'jyuser'
----------------------------------------------------------------------------------------------
--主键
ALTER TABLE [GBTerminal].[TerminalDec] DROP CONSTRAINT [PK__Terminal__C451DB3124C84789];
ALTER TABLE [GBTerminal].[TerminalDec] ADD CONSTRAINT [PK_TerminalDec] PRIMARY KEY CLUSTERED
(
[Tid] ASC
)
GO
--建唯一索引注意sp_helpconstraint不会显示索引，要用sp_helpindex
DROP INDEX [IX_TerminalDec_useid] ON [GBTerminal].[TerminalDec]
CREATE UNIQUE NONCLUSTERED INDEX [IX_TerminalDec_useid] ON [GBTerminal].[TerminalDec]
(
[AreaCode] ASC,
[useid] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX IX_F10_1 ON GBTerminal.F10 (tid,txdz)
--外键
ALTER TABLE GBTerminal.F27 WITH CHECK ADD CONSTRAINT [FK_F10] FOREIGN KEY(tid,cldh)
REFERENCES GBTerminal.f10(tid,cldh)
ON UPDATE CASCADE ON DELETE no action
--check检查
alter table GBTerminal.F11
add constraint CK_mcsx check(mcsx<4)
--默认值
ALTER TABLE GBTerminal.F10 ADD CONSTRAINT
DF_F10_txmm DEFAULT ('1') FOR txmm
--链接服务器
EXEC master.dbo.sp_addlinkedserver @server = N'58.18.173.3', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'58.18.173.3',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
--select * from [58.18.173.3].ch51.dbo.meter where rtu_id=11
go
--更改sa密码（已知sa现密码）
--exec sp_password Null,'teracypwd,'sa'
go
--给视图加扩展属性
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电表视图' ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_meter'
go
--给表字段加扩展属性
EXEC sys.sp_addextendedproperty
@level0type=N'SCHEMA', @level0name=N'GBTerminal',
@level1type=N'TABLE',@level1name=N'f10',
@level2type=N'COLUMN',@level2name=N'Stamp',
@name=N'MS_Description',@value=N'总分表类型';
--将某一个表的一列查出来用逗号隔开,用游标
declare @OutputStr varchar(2048);
declare @fn varchar(64);
set @OutputStr='';
declare cur1 cursor for
select tid from GBTerminal.TerminalDec
open cur1;
fetch next from cur1 into @fn;
while (@@fetch_status=0)
begin
set @OutputStr=@OutputStr+@fn+',';
fetch next from cur1 into @fn;
end
print @OutputStr
select @OutputStr
close cur1;
deallocate cur1;
---查看数据中所有的触发器
select * from sys.objects where type='tr'
--查看某个表的触发器的文本：
sp_helptext 'tigExtend'
--查看某个表的触发器：
sp_helptrigger 'meter'