Cyberiafreak

"Fortunate are those who take the first steps.” ― Paulo Coelho

Sql tricky queries

Here is the few queries, i tried and collected from some sql websites…

use salesport
EXEC Master..xp_cmdshell ‘Dir c:\’
/* 1.FOR DISPLAYING ALL THE TABLES IN A DATABASE */
SELECT * FROM INFORMATION_SCHEMA.TABLES

/* 2.FOR DELETING FILES FROM SQL */
EXEC master..xp_cmdshell ‘del C:\MAHE.txt’

/* 3. Use Sysobjects in SQL Server to Find Useful Database Information */
select * from sysobjects where xtype=’U’

/*4.to check sql service starting time */
SELECT crdate AS ‘SQL Server service started approximately at:’
FROM master.dbo.sysdatabases
WHERE name = ‘tempdb’

/*5.  to display date */
SELECT CONVERT(char,GETDATE(),107)
SELECT CONVERT(char,GETDATE(),106)
SELECT CONVERT(char,GETDATE(),105)
SELECT CONVERT(char,GETDATE(),104)

/*6.How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions? */

DECLARE @Date datetime
SET @Date = ‘2001/08/31’

SELECT DATEADD(dd,-(DATEPART(dw, @Date) – 1),@Date) AS ‘First day of the week’
SELECT DATEADD(dd,-(DATEPART(dw, @Date) – 7),@Date) AS ‘Last day of the week’
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS ‘Last day of the month’

/*7. showing all the device installed */
sp_helpdevice  

/*8. to show the db status */
sp_helpdb

content : original src: extremeexperts.com

Mahe~

Advertisements

March 15, 2005 - Posted by | SQL SERVER - My Own code

No comments yet.

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

%d bloggers like this: