SQL Server命令

1. 查询当前数据库下的所有用户表

    select name from {DatabaseName}.sys.SysObjects where xtype = 'U' and category = 0

2. 查询对象ID

    select OBJECT_ID('object')

 

3. 打开语句执行时间统计

    set statistics time on

 

4. 查询数据库ID，名称   

    select DB_ID(), DB_NAME()

 

5. 查询数据库"恢复模式"更改记录 

select * from ::fn_dblog(null, null) where operation = 'LOP_PREP_XACT'

 6. 删除作业

exec msdb.dbo.sp_delete_job @job_name=N'<job_name>'

7. 查询活动日志起始LSN

DBCC OPENTRAN

数据库 'testdb1' 的事务信息。

已复制的事务信息:
        最早的分布式 LSN     : (0:0:0)
        最早的非分布式 LSN : (62:337:1)
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

8. 查询日志空间使用量

DBCC SQLPERF(LOGSPACE)

9. 查看虚拟日志文件VLF(virtual log file)

DBCC LOGINFO

10. 查看所有数据类型

select * from systypes

 9. 查询表结构

     oracle 和 mysql 查看表结构都用 “desc tablename”

    sqlserver却不是...

    查看命令："sp_help [table_name]"

    如

    

    也可以通过如下SQL语句查询

SELECT sc.colorder, sc.name, st.name as 'datatype', sc.length 
  FROM syscolumns sc, systypes st
  WHERE sc.xusertype = st.xusertype and sc.id = OBJECT_ID('dbo.t1')
  ORDER BY colorder

10. 创建带有主键(PK)的表

create table dbo.t2
	(Id INT NOT NULL, 
	 Name VARCHAR(20),
	 CONSTRAINT pk_id PRIMARY KEY (ID))

11. 创建带有索引(UK)的表

create table dbo.t3(
	col1 INT UNIQUE,
	col2 INT UNIQUE,
	col3 INT UNIQUE,
	col4 VARCHAR(20)
)

12. 添加主键

  建表命令: 

create table t1 (id INT, name VARCHAR(20))

  创建表时未设置主键，后期添加主键的方法

  1) 设置字段不能为空

alter table t1 alter column ID int NOT NULL

  2) 添加主键

alter table t1 add constraint pk1 primary key(ID)

13. 查询主键

select 
	* 
from 
	information_schema.table_constraints 
where 
	constraint_schema='dbo'
	and table_name='t3'
	and constraint_type='PRIMARY KEY'

14. 查询主键所含的列(dbo.t1)

SELECT col.column_name, tab.CONSTRAINT_TYPE 
FROM 
    information_schema.table_constraints tab, 
    information_schema.constraint_column_usage col 
WHERE 
    col.constraint_name = tab.constraint_name
    AND col.table_name = tab.table_Name
    AND constraint_type = 'PRIMARY KEY'
    AND col.table_schema = 'dbo'
    AND col.table_Name = 't1'

15. 查询唯一索引

SELECT col.column_name, tab.CONSTRAINT_TYPE 
FROM 
    information_schema.table_constraints tab, 
    information_schema.constraint_column_usage col 
WHERE 
    col.constraint_name = tab.constraint_name
    AND col.table_name = tab.table_Name
    AND constraint_type = 'UNIQUE'
    AND col.table_schema = 'dbo'
    AND col.table_Name = 't1'    

16. 查询所有用户表

select st.name, st.object_id, sc.name as 'schema'
  from sys.tables st, sys.schemas sc
  where st.schema_id = sc.schema_id
    and st.type = 'U' 
    and st.is_ms_shipped = 0

17. 按事物提交

BEGIN TRANSACTION
insert into dbo.t1 values(2, '222')
insert into dbo.t1 values(3, '333')
COMMIT TRANSACTION

18. 回滚

BEGIN TRANSACTION
insert into dbo.t1 values(4, '444')
ROLLBACK TRANSACTION

19. 创建schema

create schema myschema
create table myschema.t2(ID INT, NAME VARCHAR(20))

20. 创建一张包含所有列类型的表

create table dbo.alltype (
  col_tinyint         tinyint,  
  col_smallint        smallint,
  col_int             int,
  col_bigint          bigint,
  
  col_float           float,
  col_real            real,
  col_decimal         decimal(5, 2),
  col_numeric         numeric(5, 2),
  
  col_char            char(100),
  col_varchar         varchar(200),
  col_nchar           nchar(100),
  col_nvarchar        nvarchar(200),
  col_text            text,
  col_ntext           ntext,
  
  col_date            date,
  col_time            time,
  col_timestamp       timestamp,
  col_datetime        datetime,
  col_datetime2       datetime2,
  col_smalldatetime   smalldatetime,
  col_datetimeoffset  datetimeoffset,
  
  col_money           money,
  col_smallmoney      smallmoney, 
  
  col_binary          binary(100),
  col_varbinary       varbinary(200),
  col_bit             bit,
  col_image           image,
  
  col_xml             xml,
  
  col_sysname         sysname,
  
  col_geometry        geometry,
  col_geography       geography
)

往这个表中插入数据

insert into dbo.alltype   
values(1, 
       2, 
       3, 
       4,
      
       5.1, 
       6.13, 
       7.11, 
       324.22, 
       
       'I am char', 
       'I am varchar', 
       'nchar', 
       'nvarchar', 
       'text', 
       'ntext',
           
       '2016-01-02', 
       '13:54:51.1234567', 
       DEFAULT, 
       '2016-01-02 13:54:51.123', 
       '2016-01-02 13:54:55.1234567', 
       '2016-01-02 13:56', 
       '2016-01-02 13:57:51.1234567+08:00',
     
       123.45, 
       123.67,
     
       0x010203, 
       0x04050607, 
       1, 
       0x08090A0B,
     
       '<hello>123</hello>',
     
       'aaa',  
     
       geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0),
       geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326)
     )  

21. 查看存储过程的定义

      例如，已有一个存储过程"sys.sp_cdc_enable_table"

      查看这个存储过程的定义

EXEC sp_helptext N'sys.sp_cdc_enable_table'

22. 查询作业

select * from msdb.dbo.sysjobs

23. 当某个作业存在时将其删除

-- delete job 'myjob' if exists
if EXISTS(select name from msdb.dbo.sysjobs where name = 'myjob')
EXEC msdb.dbo.sp_delete_job @job_name=N'myjob'

24. 删除复制作业(2005上测试)

sp_removedbreplication testdb4

25. 查看备份历史

SELECT * FROM msdb.dbo.backupset WHERE database_name='数据库名'