1.SQL Server 2000中查詢表名 經(jīng)常碰到一些忘記表名稱的情況,此時(shí)只記得個(gè)大概,此時(shí)可通過查詢系統(tǒng)表Sysobjects找到所要的表名,如要查找包含用戶的表名,可通過以下SQL語句實(shí)現(xiàn), Select * From sysobjects Where name like '%user%' 2.如果知道列名,想
1.SQL Server 2000中查詢表名
經(jīng)常碰到一些忘記表名稱的情況,此時(shí)只記得個(gè)大概,此時(shí)可通過查詢系統(tǒng)表Sysobjects找到所要的表名,如要查找包含用戶的表名,可通過以下SQL語句實(shí)現(xiàn),
Select *
From sysobjects
Where name like '%user%'
2.如果知道列名,想查找包含有該列的表名,可加上系統(tǒng)表syscolumns來實(shí)現(xiàn),如想查找列名中包含有user的所有表名,可通過以下SQL語句來實(shí)現(xiàn)
Select *
From sysobjects s
Where Exists(
Select *
From syscolumns
Where ID = s.ID and name like '%user%'
)
3 .SQL SERVER
查看所有表名:
select name from sysobjects where type='U'
查詢表的所有字段名:
Select name from syscolumns Where ID=OBJECT_ID('表名')
select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
4.ACCESS
查看所有表名:
select name from MSysObjects where type=1 and flags=0
MSysObjects是系統(tǒng)對(duì)象,默認(rèn)情況是隱藏的。通過工具、選項(xiàng)、視圖、顯示、系統(tǒng)對(duì)象可以使之顯示出來。
1.獲取表的基本字段屬性
--獲取SqlServer中表結(jié)構(gòu)
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns, systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = object_id('你的表名')
2.獲取字段的描述信息
--獲取SqlServer中表結(jié)構(gòu) 主鍵,及描述
declare @table_name as varchar(max)
set @table_name = '你的表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id
3.單獨(dú)查詢表的遞增字段
--單獨(dú)查詢表遞增字段
select [name] from syscolumns where
id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1
4.獲取表的主外鍵
--獲取表主外鍵約束
exec sp_helpconstraint '你的表名' ;
5.相當(dāng)完整的表結(jié)構(gòu)查詢
--很全面的表結(jié)構(gòu)
exec sp_helpconstraint '你的表名' ;
SELECT 表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
序 = a.colorder,
字段名= a.name,
標(biāo)識(shí) = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
主鍵 = CASE
WHEN EXISTS ( SELECT * FROM sysobjects WHERE xtype='PK'
AND name IN (SELECT [name] FROM sysindexes WHERE id=a.id
AND indid IN (SELECT indid FROM sysindexkeys WHERE id=a.id
AND colid IN (SELECT colid FROM syscolumns WHERE id=a.id
AND name=a.name)))) THEN '√' ELSE '' END,
類型= b.name,
字節(jié)數(shù)= a.length,
長(zhǎng)度 = COLUMNPROPERTY(a.id,a.name,'Precision'),
小數(shù) = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) WHEN 0 THEN '' ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR) END,
允許空= CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
默認(rèn)值= ISNULL(d.[text],''),
說明 = ISNULL(e.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype=b.xusertype
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
LEFT JOIN syscomments d ON a.cdefault=d.id
LEFT JOIN sys.extended_properties e ON a.id=e.class AND a.colid=e.minor_id
ORDER BY c.name, a.colorder
6.獲取所有的庫(kù)名
--獲取服務(wù)器中的所有庫(kù)名
select * from mastersysdatabases
7.獲取服務(wù)器上所有庫(kù)的所有表
--獲取服務(wù)器上所有庫(kù)的所有表名
use master
declare @db_name varchar(100)
declare @sql varchar(200)
declare cur_tables cursor
for
select name from sysdatabases /*where name like 'by_%'*/
open cur_tables
fetch next from cur_tables into @db_name
while @@fetch_status = 0
begin
--set @db_name = @db_name + '.dbo.sysobjects'
print @db_name
set @sql = 'select * from ' + @db_name + '.dbo.sysobjects where xtype =''U'''
exec (@sql)
fetch next from cur_tables into @db_name
end
close cur_tables
deallocate cur_tables
go
1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.分頁(yè)SQL語句
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 結(jié)束位置
3.獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有用戶表
select * from sysobjects where xtype='U' and category=0
4.獲取某一個(gè)表的所有字段
select name from syscolumns where id=object_id('表名')
5.查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
6.查看當(dāng)前數(shù)據(jù)庫(kù)中所有存儲(chǔ)過程
select name as 存儲(chǔ)過程名稱 from sysobjects where xtype='P'
7.查詢用戶創(chuàng)建的所有數(shù)據(jù)庫(kù)
select * from mastersysdatabases D where sid not in(select sid from mastersyslogins where)
或者
select dbid, name AS DB_NAME from mastersysdatabases where sid <> 0x01
8.查詢某一個(gè)表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = '表名'
9.使用事務(wù)
在使用一些對(duì)數(shù)據(jù)庫(kù)表的臨時(shí)的SQL語句操作時(shí),可以采用SQL SERVER事務(wù)處理,防止對(duì)數(shù)據(jù)操作后發(fā)現(xiàn)誤操作問題
開始事務(wù)
Begin tran
Insert Into TableName Values(…)
SQL語句操作不正常,則回滾事務(wù)。
回滾事務(wù)
Rollback tran
SQL語句操作正常,則提交事務(wù),數(shù)據(jù)提交至數(shù)據(jù)庫(kù)。
提交事務(wù)
Commit tran
10. 按全文匹配方式查詢
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'%[^a-zA-Z0-9]China'
OR 字段名 LIKE N'China[^a-zA-Z0-9]%'
OR 字段名 LIKE N'China
11.計(jì)算執(zhí)行SQL語句查詢時(shí)間
declare @d datetime
set @d=getdate()
select * from SYS_ColumnProperties select [語句執(zhí)行花費(fèi)時(shí)間(毫秒)]=datediff(ms,@d,getdate())
12、說明:幾個(gè)高級(jí)查詢運(yùn)算詞
A: UNION 運(yùn)算符
Copyright ? 2019- 91gzw.com 版權(quán)所有 湘ICP備2023023988號(hào)-2
違法及侵權(quán)請(qǐng)聯(lián)系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)