MSSQL Tips

Listing all user created table names
============================

select name from dbname..sysobjects where xtype = ‘U’;

—————————————————————

USE YourDBName
SELECT * FROM sys.Tables

—————————————————————

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’

Listing index details of a table
======================

EXEC sp_helpindex ‘tablename’

Listing columns of a table
===================

1. select * from information_schema.columns where TABLE_NAME = ‘tablename’
2. exec sp_columns ‘tablename’


Grouping by week
=============

select DATEPART (wk, date_column) as WeekNum, count(*) as total
from table_name group by DATEPART (wk, date_column)

Above query gives results based on week numbers (1,2,3… 52)

Sample output
===========
1 – 30293
2 – 343423
3 – 897902



52 – 379829

select date_column – (DATEPART(DW, date_column) – 1) as week_start_date, count(*) as total
from table_name group by date_column – (DATEPART(DW, date_column) – 1)

Sample output
===========
2010-01-02 – 30293
2010-01-09 – 343423
2010-01-16 – 897902



2010-12-25 – 379829

NOTE:

=====

Below condition can be added to where clause if we want to omit results of current week, because current week has not yet completed.

Declare @todaydate smalldatetime = GetDate();

date_column – (DATEPART(DW,  date_column) – 1)  !=  convert(date, @todaydate- (DATEPART(DW,  @todaydate)- 1))

 

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

%d bloggers like this: