--查詢表名包含某字符

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]

?

---查看數(shù)據(jù)庫(kù)表的約束

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(' 數(shù)據(jù)庫(kù)表名 ')

?

---查看數(shù)據(jù)庫(kù)表的外鍵

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=' 數(shù)據(jù)庫(kù)表名 '
ORDER BY [Table], [Column]

?

-----SqlServer如何查詢表的列數(shù)

select count(*) from sysobjects a join syscolumns b
on a.id=b.id
where a.name='表名'

?

------?MSSQL查詢所有沒有主鍵的表

----?SQL只復(fù)制表結(jié)構(gòu)操作 (不如直接右鍵數(shù)據(jù)表使用Creaate腳本好用)

--復(fù)制表結(jié)構(gòu)有句型的
--跨數(shù)據(jù)庫(kù)
--復(fù)制結(jié)構(gòu)+數(shù)據(jù)
select *? into 數(shù)據(jù)庫(kù)名.dbo.新表名?? from? 數(shù)據(jù)庫(kù)名.dbo.原表名
--只復(fù)制結(jié)構(gòu)?
select *? into 數(shù)據(jù)庫(kù)名.dbo.新表名?? from? 數(shù)據(jù)庫(kù)名.dbo.原表名 where 1=0
--復(fù)制到臨時(shí)表
select *? into #temptablename?? from? 數(shù)據(jù)庫(kù)名.dbo.原表名 where 1=0?

--跨服務(wù)器數(shù)據(jù)庫(kù)
select * into 本地庫(kù)名.表名 from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=遠(yuǎn)程ip;User ID=sa;Password=密碼'
).庫(kù)名.dbo.表名

?

----?SQL批量更新數(shù)據(jù)

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

?

-----實(shí)現(xiàn)刪除表中某列有重復(fù)值的數(shù)據(jù)行

delete Bms_StoppageReason from Bms_StoppageReason a
where exists(select * from Bms_StoppageReason
where id<a.id and Code=a.Code)

?

----將一個(gè)表中某個(gè)字段的值全部更新到另外一個(gè)表相應(yīng)的字段

update sp set sp.ParentID =sr.ID
from Bms_StoppagePhenomenon sp join Bms_StoppageReason sr
on sr.Code=sp.Descriptio

select col2,col4 from @t a
WHERE col3=(SELECT max(col3) FROM @t WHERE col2=a.col2)

?

----------- ---- ----------------------- ----------- -----------

drop table #Tmp --刪除臨時(shí)表#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中分組序號(hào)

例如：
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
每個(gè)小組有次序號(hào)。

----------》》

假設(shè)表名為 test1, 字段名為 a, b

SQL 語(yǔ)句如下：

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*]

?

----------------按行號(hào)刪除

delete T from (select *,row_number() over(order by ProductID,SHID) as RowIndex

from [Bms_Store_Current] ) ?T?where T.RowIndex>2988

?

?

    假設(shè)將table1表的column1改名為column2,可以使用如下:
    

    
      sp_rename
    
     'table1.column1','column2','column'
    

    

    

  

SQL Server批量刪除數(shù)據(jù)庫(kù)表------------------------------------------

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

    

    
      
        先找出你的外鍵所在用下面的語(yǔ)句：--查詢一個(gè)表的所有外鍵:------------------------------------------
      
    
  

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)
,級(jí)聯(lián)更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
,級(jí)聯(lián)刪除=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_%'

?

-----------------比較兩個(gè)數(shù)據(jù)庫(kù)表的架構(gòu)-------------------------

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語(yǔ)句獲得服務(wù)器名稱和IP地址-------------------------

sql 語(yǔ)句 收集