SQLserver数据库常用命令

 

--所有库名
SELECT * FROM sys.databases where name='ipms4s_hrxj_jk'
--表信息
select name,create_date ,modify_date from sys.tables where name='Car_GpsData'
select * from sysobjects where xtype= 'U'


--表字段信息
select column_name,data_type,'-' as column_comment from information_schema.columns
where table_name='RPS_CaseData' and table_schema='dbo' 


--表创建时间、更新时间、行数
select schema_name(t.schema_id) as [Schema], 
t.name as TableName,
t.create_date ,
t.modify_date,
i.rows as [RowCount]
from sys.tables as t, sysindexes as i
where t.name='RPS_CaseData' and  t.object_id = i.id and i.indid <=1

--所有schema信息
select * from sys.schemas s 
--库占用空间
exec sp_spaceused 
--表占用空间
exec sp_spaceused 'dbo.RPS_CaseData'

 补充

--查询表元数据
select top 1 schema_name(t.schema_id) as [Schema],  
t.name as TableName, 
convert(varchar(20),t.create_date ,120) as create_date , 
convert(varchar(20),t.modify_date ,120) as modify_date, 
i.rows as [RowCount], 
f.value as [TableComment] 
from ${db}.sys.tables as t  
left join ${db}.sys.sysindexes as i 
on t.object_id = i.id 
left join  
%1$s.sys.extended_properties f  
on  t.object_id=f.major_id  
where t.name='${table}' and    i.indid <=1 

--查询字段
select column_name,data_type,'-' as comment,COLUMN_DEFAULT from ${db}.information_schema.columns
 where table_name='${table}' 


--查询表空间
use ${db};exec sp_spaceused '${table}'

 

--查询主键
select b.column_name from ${db}.information_schema.table_constraints a 
 inner join 
${db}.information_schema.CONSTRAINT_COLUMN_USAGE b 
 on a.CONSTRAINT_NAME =b.CONSTRAINT_NAME 
where a.CONSTRAINT_TYPE ='PRIMARY KEY' and a.TABLE_NAME ='${table}'

--分区字段
select c.name  from ${db}.sys.tables t  
join ${db}.sys.index_columns ic 
on t.object_id =ic.object_id 
join  ${db}.sys.columns c
on ic.object_id =c.object_id  and ic.column_id =c.column_id 
where  t.name='${table}' and ic.partition_ordinal >0