Gokul's Blog

SQL Snippet Series

Leave a comment

To get a list of tables in a database.

select table_name as Name from INFORMATION_SCHEMA.Tables
where TABLE_TYPE ='BASE TABLE'
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
GO
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];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];
GO

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];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = ‘P’ OR [type] = ‘X’ OR [type] = ‘PC’)
AND [is_ms_shipped] = 0
ORDER BY [name];
GO

EDIT: Aug/19/2008.

Create a Comma Delimited List Using SELECT

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s