Difference between revisions of "mssql notes"
From thelinuxwiki
(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
