Difference between revisions of "mssql notes"

From thelinuxwiki
Jump to: navigation, search
 
(9 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
 
logs
 
  /var/opt/mssql/log/
 
  /var/opt/mssql/log/
Line 31: Line 53:
 
                       users
 
                       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