Mssql

From Halfface
Revision as of 12:11, 18 September 2014 by Ekaanbj (talk | contribs) (→‎show time)
Jump to navigation Jump to search

Connect to database.

# tsql -S 192.168.0.1 -U user -P password -p 1433
locale is "en_US.utf-8"
locale charset is "UTF-8"
using default charset "UTF-8"

Show tables.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
go

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
master  dbo     spt_fallback_db BASE TABLE
master  dbo     spt_fallback_dev        BASE TABLE
master  dbo     spt_fallback_usg        BASE TABLE
master  dbo     spt_monitor     BASE TABLE
master  dbo     spt_values      BASE TABLE
(5 rows affected)

show databases

SELECT name FROM master..sysdatabases
go
name
master

which version of mssql

select @@microsoftversion/0x01000000 as version
go

show time

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Uptime in minutes

select datediff(minute,create_date, getdate()) as minutes from master.sys.databases    where name='tempdb'
select datediff(minute,crdate,      getdate()) as minutes from master.dbo.sysdatabases where name='tempdb'