mssql notes

From thelinuxwiki
Revision as of 17:52, 25 May 2021 by Nighthawk (Talk | contribs)

Jump to: navigation, search

command examples

login vi CLI

sqlcmd -S 192.168.1.1 -U sa -P

change sa password

ALTER LOGIN [sa] WITH PASSWORD=N'abc123'

show list of all databases

1> SELECT name, database_id, create_date FROM sys.databases;  
2> GO  

show list of all tables

SELECT * FROM <myDBname>.INFORMATION_SCHEMA.TABLES;
GO

where <myDBanme> is replaced with DB name of interest


show all columns and schema for a table

1> SELECT * FROM myDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable_of_interest'

same as above but parsed from command line for easier reading...

sqlcmd -S 192.168.1.1 -U SA -P ******** -Q "SELECT * FROM myDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable_of_interest' " | sed 's/ \+/ /g' | grep -v "\-\-\-"

      • note - replace *192.168.1.1, *******, myDB and myTable_of_interest with your parameters

other stuff

logs

/var/opt/mssql/log/

config file

/var/opt/mssql/mssql.conf

config utility

/opt/mssql/bin/mssql-conf --help
usage: mssql-conf [-h] [-n]  ...
positional arguments:
 
   setup             Initialize and setup Microsoft SQL Server
   set               Set the value of a setting
   unset             Unset the value of a setting
   list              List the supported settings
   get               Gets the value of all settings in a section or of an
                     individual setting
   traceflag         Enable/disable one or more traceflags
   set-sa-password   Set the system administrator (SA) password
   set-collation     Set the collation of system databases
   validate          Validate the configuration file
   set-edition       Set the edition of the SQL Server instance
   validate-ad-config
                     Validate configuration for Active Directory
                     Authentication
   setup-ad-keytab   Create a keytab for SQL Server to use to authenticate AD
                     users

check for connected clients

1> sp_who2;
2> go

links

Troubleshoot SQL Server on Linux

Install SQL Server Agent on Linux - agent runs scheduled jobs

How To Set up a Microsoft SQL Server ODBC Data Source