Gokul's Blog

Leave a comment

SQL Snippet Series

To get a list of tables in a database.

select table_name as Name from INFORMATION_SCHEMA.Tables
order by name asc
To Restore a Database:
RESTORE DATABASE [MyDatabase] FROM DISK = N'E:\Backups\MYDBBackup_20080709.DMP' WITH FILE =1,
MOVE N'XList_Import_Data' TO N'E:\Mssqldata\DataInst\MyDB.MDF',
MOVE N'XList_Import_Log' TO N'E:\Mssqldata\LogInst\MyDB.LDF', NOUNLOAD, REPLACE, STATS = 10
I would like to add some more information which I saw on SQL Server central.com. This content is taken from 
the following article: http://www.sqlservercentral.com/articles/T-SQL/63471/
Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005
USE [your_database_name_here];
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];

This coding is based on filter by the object “type”. Use the following table to determine the type of object you want returned, and then adjust the WHERE clause to only return those type(s) you wish to be returned. This listing only lists views, functions, and procedures. There are other types which you can also filter, be sure to review BOL for further information on the types

Type Initial(s)
 Type Of Object Returned
FN SQL Scalar Function
IF Inline Table Valued Function
P SQL Stored Procedure
PC CLR Stored Procedure
TF SQL Table Valued Function
V View
X Extended Stored Procedure
User-Defined Stored Procedures

USE [your_database_name_here];
SELECT * FROM sys.all_objects
WHERE ([type] = ‘P’ OR [type] = ‘X’ OR [type] = ‘PC’)
AND [is_ms_shipped] = 0
ORDER BY [name];

EDIT: Aug/19/2008.

Create a Comma Delimited List Using SELECT

USE Northwind
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + LastName
FROM Employees
SELECT @listStr

The above script can be used to get the list of rows separated by “,” for a particular column. Article source

Edit: 09/08/2008

Get the duplicate values in a SQL column

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

And to get the list of duplicate values.

SELECT *  FROM table
WHERE tablefield IN (
SELECT tablefield  FROM table
GROUP BY tablefield  HAVING (COUNT(tablefield ) > 1) )

Article Source


Edit: 09/19/2008

sp_who2 : Displays the current open connections

DBCC INPUTBUFFER(<spid>): Display the last executed command by the specified SPID.