--切换数据库
use c2
go
-- 检查是否存在表:存在就删除
if EXISTS (select * from dbo.SysObjects where id =OBJECT_ID(N'JOIN_BusinessArea') AND OBJECTPROPERTY(id,'IsTable')=1)
droptable 表名称
GO
--查询当前数据库表名
select [name] from sysObjects where xtype='U'and [name] <> 'dtproperties'orderby[name]
--查询当前数据库所有用户视图
Select [name] From sysObjects Where xtype='V'And [name]<>'syssegments'And [name]<>'sysconstraints'OrderBy [name]
--获得指定表中所有的列和类型
Select
c.name As'列名',
t.name As'列类型'From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name='offline_Product ' --指定表名 base_dict
OrderBy c.colorder
--获取当前数据库指定列名在那些表中
Select
m.name AS'表名',
c.name As'列名',
t.name As'列类型'From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U'and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
And c.name='product_id' --指定列名
OrderBy m.name
--获取当前数据库所有表列名和表类型
Select
m.name AS'表名',
c.name As'列名',
t.name As'列类型'From syscolumns c, systypes t, sysobjects o , ( select [name] from sysObjects where xtype='U'and [name] <> 'dtproperties' ) m
Where c.xtype = t.xusertype
And c.id = o.id
And o.name =m.name
OrderBy m.name
--删除表内相同数据
with ops as (select ROW_NUMBER() over(PARTITION BY SpecID orderby(select0))as rn from OFFLINE_ProductSpec)
deletefrom ops where rn >1go
--查看数据库连接
Select * from sys.dm_exec_connections --此命令可以看到有多少人在连
Select * from sys.dm_exec_sessions --此命令可以看到有多少会话,一个连接可以有多个会话
SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'BusinessPlat'selectcount(*) '连接数'from master.dbo.sysprocesses where spid>50
-- distinct用于返回唯一不同的值。
-- 实例 selectdistinct Code,BankID from (select a.Code,b.BankID from BankName as a, sys_Bank as b where b.Bank =a.Name) as c orderby c.BankID
-- 添加列altertable 表名 add 新增字段名 字段类型 默认值;-- 示例:alter table tb add col1 int 0;-- 修改行内容
CASE [列名]
WHEN '0' then '正常'
when '-1' then '已删除'
ENDAS [列名]
-- 拼接字符串
--STUFF('',1,1,'')
--综合示例(修改行内容 + 拼接字符串 ):
select t1.DetailPID,t1.Price,t1.SpecialPrice,
CASE t1.[Status]
WHEN'0'then'正常'when'-1'then'已删除'ENDAS [status]
,t1.PBarCode,STUFF(
(
select' '+t3.Name+': '+t2.SpecValue
from OFFLINE_ProductDetail2Spec t2
leftjoin OFFLINE_ProductSpec t3
on t2.SpecID = t3.SpecID
where t2.DetailPID = t1.DetailPID orderby t2.SpecID
FOR XML PATH('')
),1,1,''
) spec
from OFFLINE_ProductDetail as t1 innerjoin
(
select * from OFFLINE_Product where PID='P001607010000004'and BusinessAreaID='BA011603020002'
) as t0 on t1.PID = t0.PID