Mssql: Difference between revisions

From Halfface
Jump to navigation Jump to search
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Connect to database.==
==Connect to database.==
  # tsql -S 192.168.0.1 -U user -P password -p 1433
  # tsql -H 192.168.0.1 -U user -P password -p 1433
  locale is "en_US.utf-8"
  locale is "en_US.utf-8"
  locale charset is "UTF-8"
  locale charset is "UTF-8"
  using default charset "UTF-8"
  using default charset "UTF-8"
=whoami=
select suser_name()
go


==Show tables.==
==Show tables.==
Line 38: Line 41:
  select datediff(minute,create_date, getdate()) as minutes from master.sys.databases    where name='tempdb'
  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'
  select datediff(minute,crdate,      getdate()) as minutes from master.dbo.sysdatabases where name='tempdb'
==use database==
USE DATABASE
==show permissions==
SELECT
    class_desc
  , CASE WHEN class = 0 THEN DB_NAME()
        WHEN class = 1 THEN OBJECT_NAME(major_id)
        WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name
  , state_desc
FROM sys.database_permissions
==command line with queuery==
tsql -H 192.168.0.1 -U user -P password -p 1433 -o q < /tmp/tsql
==queries running==
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
go

Latest revision as of 14:33, 24 April 2022

Connect to database.

# tsql -H 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"

whoami

select suser_name()
go

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

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'

use database

USE DATABASE

show permissions

SELECT 
   class_desc 
 , CASE WHEN class = 0 THEN DB_NAME()
        WHEN class = 1 THEN OBJECT_NAME(major_id)
        WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
 , USER_NAME(grantee_principal_id) [User]
 , permission_name
 , state_desc
FROM sys.database_permissions

command line with queuery

tsql -H 192.168.0.1 -U user -P password -p 1433 -o q < /tmp/tsql

queries running

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
go