SQL Server Command List

Below are some useful TitBits when working with SQL Server. I would append this list quite often with more tips in future.

Please feel free to send in your tips to be published that would be helpful to others. You can post them on the comments part of this article below.

SQL Commands:

1. How can i get the local SQL Server Name?
SELECT @@SERVERNAME

2. How to get the list of .mdf (data files) & .ldf (log files) files of all databases and their details for an SQL Server?
SELECT * FROM SYS.MASTER_FILES

3. How to get the database id for a given database?
SELECT DB_ID('<Database_Name>')

4. How to check if a particular table exists in a database?
SELECT * FROM <Database_Name>..sysobjects WHERE NAME LIKE '<Table_Name>'

5. How to get only data file details of a particular database?
EXEC ('DBCC SHOWFILESTATS')

Note: Run this command by selecting the database for whose datafile details you want to view.

6. How to get the LogSpace details of all the database in a SQLServer?
EXEC('DBCC SQLPERF (Logspace)')

7. How to rename a database?
sp_renamedb 'oldname' , 'newname'
If the database is being used by someone, sp_renamedb will not work. In that case,
- first get the db to a single user mode using sp_dboptions.
- use sp_renamedb to rename the db.
- use sp_dboptions to change the db to the multi user mode.

8. How do i get information about the remote server?
sp_helpserver [server_name]

9. How can i see the text of a procedure, trigger, view, default, rule, integrity check constraint?
sp_helptext [object_name]

10. How can i get details on any SQL Server object (for eg. table)?
sp_help [table_name/ojbect_name]

11. How do i get the list of processes that currently hold locks?
sp_lock spid1 [,spid2] (you can check for multiple spid's)

where spid1, spid2 - is the SQL Server process ID number of a lock.

12. How can i get a SPID information about all SQL Server users and processes?
sp_who or sp_who2

13. How can i check the machine hard drive space details?
EXEC xp_fixeddrives

14. How can i get list of users in the current database?
EXEC sp_helpuser

15. How can i get the list of logins for each database?
EXEC sp_helplogins

16. How can i get the number of rows affected by the last statement in a stored proc?
SELECT @@ROWCOUNT

17. How do i get the space, index, row counts of a SQL Table?
EXEC sp_spaceused <table>

18. How do i get the list of stored procedures in a database?
SELECT [name] FROM sysobjects WHERE [type] = 'p'

19. How do i get the list of tables in a database?
SELECT [name] FROM sysobjects WHERE [type] = 'u'

20. How can i debug a stored procedure execution in a production environment?
Use SQL Profiler.