--查詢表名包含某字符
Select *
From sysobjects
Where name like '%Area%' and type='u'
?
-- 含有'XXX'字段的所有表
select a.[name] TableName from sysobjects a,
(
select [id],count(*) b from syscolumns
where [name] ='SupplierREF'
group by [id]
)
b where a.[id]=b.[id]
?
---查看數據庫表的約束
select a.name as DFName,b.Name as ColumnName
from sys.objects a inner join sys.columns b on a.object_id=b.default_object_id
where a.type='D'
and b.object_id=object_id('
數據庫表名
')
?
---查看數據庫表的外鍵
SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where Table_Name='
數據庫表名
'
ORDER BY [Table], [Column]
?
-----SqlServer如何查詢表的列數
select count(*) from sysobjects a join syscolumns b
on a.id=b.id
where a.name='表名'
?
------?MSSQL查詢所有沒有主鍵的表
EXCEPT
SELECT?TABLE_NAME?FROM?INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE?CONSTRAINT_TYPE?=?'PRIMARY?KEY'
----?SQL只復制表結構操作 (不如直接右鍵數據表使用Creaate腳本好用)
--復制表結構有句型的
--跨數據庫
--復制結構+數據
select *? into 數據庫名.dbo.新表名?? from? 數據庫名.dbo.原表名
--只復制結構?
select *? into 數據庫名.dbo.新表名?? from? 數據庫名.dbo.原表名 where 1=0
--復制到臨時表
select *? into #temptablename?? from? 數據庫名.dbo.原表名 where 1=0?
--跨服務器數據庫
select * into 本地庫名.表名 from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=遠程ip;User ID=sa;Password=密碼'
).庫名.dbo.表名
?
----?SQL批量更新數據
UPDATE [Bms_Areas]
set ParentID =X.ParentID
,[AreaCode] = X.[AreaCode]
,[AreaName] = X.[AreaName]
,[Notes] =X.[Notes]
,[LevelNum] = X.[LevelNum]
,[IsDefault] = X.[IsDefault]
,[IsValid] = X.[IsValid]
,[IsDel] = X.[IsDel]
from [Bms_Areas] T, DGbell_erp.dbo.[Bms_Areas222] X
where T.AreaID =X.AreaID
?
-----實現刪除表中某列有重復值的數據行
delete Bms_StoppageReason from Bms_StoppageReason a
where exists(select * from Bms_StoppageReason
where id<a.id and Code=a.Code)
?
----將一個表中某個字段的值全部更新到另外一個表相應的字段
update sp set sp.ParentID =sr.ID
from Bms_StoppagePhenomenon sp join Bms_StoppageReason sr
on sr.Code=sp.Descriptio
-----SQL分組取最新值
select 1,'A','2011-09-08 08:38:57.870',8,null union all
select 2,'A','2011-09-20 08:38:57.870',25,null union all
select 3,'A','2011-10-03 08:38:57.870',12,null union all
select 4,'B','2011-10-15 08:38:57.870',1,null union all
select 5,'B','2011-10-19 08:38:57.870',20,null union all
select 6,'B','2011-11-02 08:38:57.870',10,null union all
select 7,'C','2011-09-20 08:38:57.870',0,null union all
select 8,'C','2011-10-03 08:38:57.870',12,null union all
select 9,'C','2011-10-15 08:38:57.870',15,null
----------- ---- ----------------------- ----------- -----------
1 A 2011-09-08 08:38:57.870 8 NULL
2 A 2011-09-20 08:38:57.870 25 NULL
3 A 2011-10-03 08:38:57.870 12 NULL
4 B 2011-10-15 08:38:57.870 1 NULL
5 B 2011-10-19 08:38:57.870 20 NULL
6 B 2011-11-02 08:38:57.870 10 NULL
7 C 2011-09-20 08:38:57.870 0 NULL
8 C 2011-10-03 08:38:57.870 12 NULL
9 C 2011-10-15 08:38:57.870 15 NULL
所需結果如下:
----------- ---- ----------------------- ----------- -----------
3 A 2011-10-03 08:38:57.870 12 NULL
6 B 2011-11-02 08:38:57.870 10 NULL
9 C 2011-10-15 08:38:57.870 15 NULL
select col2,col4 from @t a
WHERE col3=(SELECT max(col3) FROM @t WHERE col2=a.col2)
?
----------- ---- ----------------------- ----------- -----------
drop table #Tmp --刪除臨時表#Tmp
create table #tmp
(
[TmpName] [varchar](50) NULL,
[TmpCount] [float] NULL
)
insert into #tmp
Select 'A', 320 union all
Select 'B', 21 union all
Select 'C', 54 union all
Select 'D', 236 union all
Select 'E', 325 union all
Select 'F', 32
?
SELECT a.[TmpName], a.[TmpCount] ,(a.[TmpCount]/b.T) as TmpPercent
FROM #tmp a, (SELECT SUM([TmpCount]) as T from #tmp )as b
?
-----------------------------sql中分組序號
例如:
111111 123
111111 345
222222 aaaa
222222 bbbb
222222 ccccc
變成: 111111 1 123
111111 2 345
222222 1 aaaa
222222 2 bbbb
222222 3 ccccc
每個小組有次序號。
----------》》
假設表名為 test1, 字段名為 a, b
SQL 語句如下:
select t.a, t.b, (SELECT COUNT(*) FROM test1 WHERE a = t.a AND b <= t.b) AS xuhao FROM test1 t ORDER BY t.a
?
?
?
----------------------------- 刪除交集,我想在table1中刪除table1和table2的交集
delete a
from table1 as a
inner join??
table2 as b on a.[field*]=b.[field*]
?
----------------按行號刪除
delete T from (select *,row_number() over(order by ProductID,SHID) as RowIndex
from [Bms_Store_Current] ) ?T?where T.RowIndex>2988
?
?
假設將table1表的column1改名為column2,可以使用如下:
sp_rename 'table1.column1','column2','column'
SQL Server批量刪除數據庫表------------------------------------------
DECLARE @Table NVARCHAR(30)
DECLARE tmpCur CURSOR FOR
SELECT name FROM sys.objects WHERE TYPE='U' AND name LIKE N'Bms_%'
OPEN tmpCur
FETCH NEXT FROM tmpCur INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(100)
SELECT @sql = 'drop table ' + @Table
EXEC(@sql)
FETCH NEXT FROM tmpCur INTO @Table
END
CLOSE tmpCur
DEALLOCATE tmpCur
先找出你的外鍵所在用下面的語句:--查詢一個表的所有外鍵:------------------------------------------
SELECT 主鍵列ID=b.rkey
,主鍵列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
,外鍵表ID=b.fkeyid
,外鍵表名稱=object_name(b.fkeyid)
,外鍵列ID=b.fkey
,外鍵列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,級聯更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,級聯刪除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
and object_name(b.rkeyid) LIKE N'Bms_%'
?
-----------------比較兩個數據庫表的架構-------------------------
select name, schema_name(schema_id) [schema] from db1.sys.objects where type = 'U'
except
select name, schema_name(schema_id) [schema] from db2.sys.objects where type = 'U'
?
----------------- 相同username 找id小的 -------------------------
select a.* from ttt_test2 as a inner join
(
select user_name,min(id) as minid From ttt_test2 Group by user_name
) as b
on a.user_name=b.user_name and id=b.minid
?
-----------------使用SQL語句獲得服務器名稱和IP地址-------------------------
SELECT
SERVERPROPERTY(
'MachineName'
)
select
@@SERVERNAME
select
HOST_NAME()
SELECT
SERVERNAME =
CONVERT
(NVARCHAR(128),SERVERPROPERTY(
'SERVERNAME'
))
,LOCAL_NET_ADDRESS
AS
'IPAddressOfSQLServer'
,CLIENT_NET_ADDRESS
AS
'ClientIPAddress'
?
FROM
SYS.DM_EXEC_CONNECTIONS
WHERE
SESSION_ID = @@SPID
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
