Thursday, 28 February 2013
SQL SERVER – Find First Day of Any Month – Current Previous Next
--FirstDay_PreviousMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)
--FirstDay_CurrentMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate()),0)
--FirstDay_NextMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)
SQL SERVER – Find Last Day of Any Month – Current Previous Next
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
Tuesday, 26 February 2013
Friday, 15 February 2013
Table rows and column count in sql server query
CREATE TABLE #temp
(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,a.row_count,COUNT(*) AS col_count FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count
DROP TABLE #temp
SQL Server database details
how many table and rows to be display
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT)
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?'
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
Subscribe to:
Posts (Atom)