Difference between revisions of "mssql notes"

From thelinuxwiki
Jump to: navigation, search
(Created page with "==command examples== login vi CLI sqlcmd -S 192.168.1.1 -U sa -P change sa password ALTER LOGIN [sa] WITH PASSWORD=N'abc123' category:database category:mssql")
 
 
(10 intermediate revisions by one user not shown)
Line 4: Line 4:
 
change sa password
 
change sa password
 
  ALTER LOGIN [sa] WITH PASSWORD=N'abc123'
 
  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==
 +
[https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide?view=sql-server-ver15 Troubleshoot SQL Server on Linux]
 +
 +
[https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent?view=sql-server-ver15 Install SQL Server Agent on Linux] - agent runs scheduled jobs
 +
 +
[https://support.microsoft.com/en-us/help/965049/how-to-set-up-a-microsoft-sql-server-odbc-data-source How To Set up a Microsoft SQL Server ODBC Data Source]
 +
 +
 
[[category:database]]
 
[[category:database]]
 
[[category:mssql]]
 
[[category:mssql]]

Latest revision as of 17:53, 25 May 2021

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