SQL server provides a rich set of commands to do operations other than the normal CRUD operations. In this blog, I will try to maintain a list of commands that are frequently used and required.
- sp_helpdb - returns a table with list of databases, their sizes, owners, created date, status and their compatibility level. Compability level is used to determine how certain features in this version will work. Usually, compatibility level 100 means SQL server 2008, 90 means SQL server 2005 etc.
- sp_helpdb <dbname> - If a database name is provided then besides the information given by sp_helpdb, this command also provides information about file allocations for the specified dbname. This table shows the name, filename, filegroup, its size, max size, growth and usage.
- select * from sys.<items> - This is a very helpful command to get a lot of information about a lot of items. By running this command, you can get information about databases, tables, columns, views, filegroups, logins, database files etc.
- select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = <tablename> - Information_schema provides a great way to get information about schemas. With this command you can find out all the information about all the columns in table tablename. Using Information_schema you can also find information about Tables, Columns, Views, Constraints etc.
- Version information - select @@version will give you all the information about the version of the SQL Server where you ran this query. It will also tell you about the edition, bit (32/64) etc.
- CTE (Common Table Expression) - is a temporary named result set.