Find Table in Every Database of SQL Server

CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases


CREATE TABLE #TmpTable 
(TABLE_CATALOG VARCHAR(128), 
TABLE_SCHEMAVARCHAR(128), 
TABLE_NAME VARCHAR(256), 
TABLE_TYPE VARCHAR(10))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *FROM #TmpTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableNameInAllDatabase 'Address'
GO

Get Date of All Weekdays or Weekends of the Year

DECLARE @Year AS INT,@FirstDateOfYear DATETIME,@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2010SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data;
WITH cte AS (SELECT 1 AS DayID,@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear)
SELECT FromDate AS Date, DaynameFROM CTEWHERE DayName IN ('Saturday','Sunday')
/*
WHERE DayName IN ('Saturday,Sunday') -- For Weekend
WHERE DayName NOT IN ('Saturday','Sunday') -- For Weekday
WHERE DayName LIKE 'Monday' -- For Monday
WHERE DayName LIKE 'Sunday' -- For Sunday
*/
OPTION (MaxRecursion 370)

Date Conversations

select
 getdate() as today_with_time,
 dateadd(day,datediff(day,0,getdate()),0) as today_without_time,
 dateadd(day,datediff(day,0,getdate())-1,0) as yesterday,
 dateadd(day,datediff(day,0,getdate())+1,0) as tomorrow,
 dateadd(month,datediff(month,0,getdate()),0)  as first_day_of_month,
 dateadd(month,datediff(month,0,getdate())+1,-1)  as last_day_of_month,
 dateadd(year,datediff(year,0,getdate()),0)  as first_day_of_year,
 dateadd(year,datediff(year,0,getdate())+1,-1)  as last_day_of_year,
 dateadd(week,datediff(week,0,getdate()),0)  as first_day_of_week,
 dateadd(week,datediff(week,0,getdate())+1,-1)  as last_day_of_week,
 dateadd(quarter,datediff(quarter,0,getdate()),0)  as first_day_of_quarter,
 dateadd(quarter,datediff(quarter,0,getdate())+1,-1)  as last_day_of_quarter,
 dateadd(hour,datediff(hour,0,getdate()),0)  as starting_time_of_current_hour,
 dateadd(hour,datediff(hour,0,getdate())+1,-1)  as ending_time_of_current_hour,
 dateadd(minute,datediff(minute,0,getdate()),0)  as starting_time_of_current_minute,
 dateadd(minute,datediff(minute,0,getdate())+1,-1)  as starting_time_of_current_minute,
 datepart(hour,getdate())  as current_hour,
 datepart(minute,getdate())  as current_minute,
 datepart(second,getdate())  as current_second,
 datepart(month,getdate())  as current_month,
 datepart(year,getdate())  as current_year,
 datepart(week,getdate())  as current_week,
 datepart(quarter,getdate())  as current_quarter,
 datepart(dayofyear,getdate())  as day_of_year,
 datename(weekday,getdate())  as current_weekday,
 datename(month,getdate())  as current_month_name

Export Data With Columns From SQL Server 2005 to Microsoft Excel Datasheet

--EXEC proc_generate_excel_with_columns 'Master','TESTA','C:\Testing.xls'


create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as


--Generate column names as a recordset


declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
--, @table_name varchar(100), @file_name varchar(max), @db_name varchar(100)
--SET @table_name='TESTA'
--SET @file_name='C:\Testing.xls'
--SET @db_name='master'
select 
@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
information_schema.columns
where 
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--select @columns 
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
--select @data_file
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -SMinds8\Minds8 -Usa -P123456 -c -t'',no_output'
exec(@sql)


--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -SMinds8\Minds8 -Usa -P123456 -c -t'',no_output'
exec(@sql)


--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)


--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)