学习数据库知识之 SQL 操作标记
来源:网络整理 责任编辑:admin 发表时间:2013-07-01 15:27 点击:次
创建某一个表:
CREATE TABLE [weekyc] (
[yc_product] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[yc_max] [numeric](18, 0) NULL ,
[yc_min] [numeric](18, 0) NULL ,
[yc_situation] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[yc_time] [datetime] NULL ,
[id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Authority] [int] NOT NULL CONSTRAINT [DF_weekyc_Authority] DEFAULT (3),
[remark] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
修改某一个表:
alter table weekyc add yc_max numeric(18,0) null
ALTER TABLE [weekyc] ADD DEFAULT '0' FOR [yc_max]
删除一个约束:
declare @name varchar(8000)
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('weekyc') and b.id=a.cdefault and
a.name='yc_max' and b.name like 'DF%'
exec('alter table weekyc drop constraint '+@name)
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
数据库执行进程:sp_cmdshell
查询/修改/删除中影响的列:
RowCount
删除表中的重复数据:
(假设ID是Identity列,Title相同者将被删除)
delete from TableName where id not in(select max(id) from TableName group by Title)
查看表信息:
select * from tablespaceinfo
清空数据库:
truncate table XX;
查看某库下面每个表占用的空间信息:
use XXDB;
select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes
where indid=1
order by reserved desc
sp_helptext XX;这是查看源
CREATE TABLE [weekyc] (
[yc_product] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[yc_max] [numeric](18, 0) NULL ,
[yc_min] [numeric](18, 0) NULL ,
[yc_situation] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[yc_time] [datetime] NULL ,
[id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Authority] [int] NOT NULL CONSTRAINT [DF_weekyc_Authority] DEFAULT (3),
[remark] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
修改某一个表:
alter table weekyc add yc_max numeric(18,0) null
ALTER TABLE [weekyc] ADD DEFAULT '0' FOR [yc_max]
删除一个约束:
declare @name varchar(8000)
select @name=b.name from syscolumns a,sysobjects b where a.id=object_id('weekyc') and b.id=a.cdefault and
a.name='yc_max' and b.name like 'DF%'
exec('alter table weekyc drop constraint '+@name)
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
数据库执行进程:sp_cmdshell
查询/修改/删除中影响的列:
RowCount
删除表中的重复数据:
(假设ID是Identity列,Title相同者将被删除)
delete from TableName where id not in(select max(id) from TableName group by Title)
查看表信息:
select * from tablespaceinfo
清空数据库:
truncate table XX;
查看某库下面每个表占用的空间信息:
use XXDB;
select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes
where indid=1
order by reserved desc
sp_helptext XX;这是查看源
相关新闻>>
- 发表评论
-
- 最新评论 进入详细评论页>>