T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

Prashanth Jayaram:

Added few more examples

Originally posted on Prashanth Jayaram :

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

The above undocumented SP’s  iterate through each database and each table of a SQL instance

Download SQL :- UndocumentSPToGetNoOfRowsAllDatabases

SQL:-

CREATE TABLE  #TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;

EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
INSERT INTO #TableRowCounts ([databaseNAme],[TableName], [RowCount])
EXEC [?].dbo.sp_MSforeachtable @command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',@replacechar = ''&'''

SELECT * FROM #TableRowCounts
					
DROP TABLE #TableRowCounts	

Note:-The above query is expensive. Please use at your risk

Output-

TableCount-spMSforeachdb&table

The use of IF and Like clause with sp_MSforeachtable

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' =''[dbo].[log]'')
BEGIN
 PRINT N''?''
 SELECT COUNT(*) from ? 
END'

EXEC [sp_MSforeachtable] @command1=N' IF (N''?'' LIKE N''%Resource%]'') BEGIN PRINT N''?'' SELECT ''?'' TABLENAME,COUNT(*)…

View original 102 more words

Posted in Uncategorized | Leave a comment

How to Replace Multiple Strings in a File using PowerShell

Replace the Data Source and Initial Catalog values of WebConfig.XML

Content of XML file

<Configuration ConfiguredType=”Property” Path=”\Package.Connections[ConnStaging].Properties[ConnectionString]” ValueType=”String”>
<ConfiguredValue>Data Source=localhost;Initial Catalog=Stage;Integrated Security=SSPI; Connection Timeout = 10</ConfiguredValue>
</Configuration>

webConfig

PARAM(
[String]$DatabaseName='DCTarget',
[String]$XML='c:\webconfig.XML',
[String]$DatabaseServer='DataCenterDB01')
[string]$db = 'Catalog='+ $DatabaseName
[string]$dbs = 'Source='+ $DatabaseServer
(Get-Content $XML) | 
Foreach-Object {
$_ -replace "Catalog=([A-Za-z0-9_]+)", $db -replace "Source=([A-Za-z0-9_]+)", $dbs } | Set-Content $XML

webConfig1

The above code can be used to modify the content of any files or even copy the modified content to new file

$Sourcefile = 'c:\filename.txt'
$desfile =  'c:\Newfilename.txt'
(Get-Content $Sourcefile) | Foreach-Object {
    $_ -replace 'Prashanth', 'Jayaram' `
       -replace 'SQL', 'PowerShell' 
    } | Set-Content $destFile

webConfig2

Posted in PowerShell, String handling | Tagged , , | Leave a comment

T-SQL – How to get the Financial Quarter details of a date field

declare @table table
(
[Paid Date] date
)

insert into @table
values('20150102'),('20150512'),('20150830'),('20151231'),('20141230')

;WITH Quarters AS (
   SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
   SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
   SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION
   SELECT Q = 'Q4', MonthBegin = 10, MonthEnd = 12
)
SELECT
   [paid date],[Quarter] = 'FSY'+CONVERT(VARCHAR(4), DATEPART(yyyy, CONVERT(DATETIME, [paid date]))) + '_' + q.Q
FROM
@table
  INNER JOIN Quarters q ON
      DATEPART(m, CONVERT(DATETIME, [paid date])) >= q.MonthBegin AND
      DATEPART(m, CONVERT(DATETIME, [paid date])) <= q.MonthEnd;

Output

QuaterDetails

 

 

Posted in SQL | Tagged , , , , | 4 Comments

T-SQL :- How to Search String in all Stored Procedures across All User Defined Databases

There are many different ways to accomplish this tasks.

Download Link https://gallery.technet.microsoft.com/T-SQL-How-to-Search-String-a1704fc6

The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string

CREATE TABLE  #ProcSearch  (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate date)


DECLARE @command varchar(1000) 
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
INSERT INTO #ProcSearch
SELECT ''?'',ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%Index_Name%'' AND (ROUTINE_TYPE=''PROCEDURE'' or ROUTINE_TYPE=''FUNCTION''); 
END' 
EXEC sp_MSforeachdb @command

SELECT * FROM #ProcSearch

DROP TABLE #ProcSearch

index2

The below SQL uses SearchString as a parameter also it uses syscomments system view

CREATE TABLE #ProcSearch ([databaseNAme] Varchar(100),ProcName VARCHAR(128))

DECLARE @command varchar(1000) 
Declare @SearchString varchar(100)='Index_Name'
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? 
INSERT INTO #ProcSearch ([databaseNAme],[ProcName])
SELECT ''?'', OBJECT_NAME(id) as ROUTINE_CATALOG FROM syscomments WHERE [text] LIKE ''%'+@SearchString+'%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id)
END' 
EXEC sp_MSforeachdb @command

SELECT * FROM #ProcSearch

index1

Reference Thread

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/857d9754-ac24-4acb-b402-e52d028bb81a/linked-servers-migration?forum=sqlgetstarted#9c7fec83-610d-4ec6-9b27-9f685031b571

Posted in SQL, T-SQL | Tagged , , , , , | Leave a comment

T-SQL – Read CSV files using OpenRowSet

We can directly access a CSV file using T-SQL.

Input file

ReadInput

Configure server to run Ad Hoc Distributed Queries

sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO

Check for Microsoft Access Driver on your system

driver

SQL

select 
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select * from C:\blog\input.CSV')

OR

select
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
DBQ=C:\blog\' 
,'select * from "input.CSV"') T

OR

select
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
 ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
 DefaultDir=C:\blog\' 
 ,'select * from "input.CSV"') T

Output:-

outpuDriver

 

Reference Link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ded961b1-f386-4780-91e7-0527271032ec/convert-data-into-xml-format?forum=transactsql#c8d8a2a6-42a7-496a-abdd-31442e4bb598

 

Posted in SQL, Uncategorized | Tagged , , , , , , | Leave a comment

T-SQL – Query to get Distinct,Sorted, Comma Separated values in to a variable

CREATE  TABLE  #temp(name char(3))

INSERT INTO #temp VALUES ('CD')
,('AB')
,('LM')
,('BC')
,('GH')
,('KJ')
,('AB')

DECLARE @cols AS NVARCHAR(MAX);

SELECT @COLS =  substring(list, 1, len(list) - 1)
FROM   (SELECT list = 
          (SELECT DISTINCT name + ','
           FROM  #temp
           ORDER BY name + ','
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T

SELECT @COLS

DROP TABLE #temp

CSV_Values

Posted in T-SQL | Tagged , , , | 9 Comments

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which undocumented SP’s being called  “sp_MSforeachdb and sp_MSforeachtable”.

The above undocumented SP’s  iterate through each database and each table of a SQL instance

Download SQL :- UndocumentSPToGetNoOfRowsAllDatabases

SQL:-

CREATE TABLE  #TableRowCounts  ([databaseNAme] Varchar(100),[TableName] VARCHAR(128), [RowCount] INT) ;

EXEC sp_MSforeachdb
@command1 = 'IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
INSERT INTO #TableRowCounts ([databaseNAme],[TableName], [RowCount])
EXEC [?].dbo.sp_MSforeachtable @command1 = ''SELECT ''''?'''',''''&'''',count(1) FROM &'',@replacechar = ''&'''

SELECT * FROM #TableRowCounts
					
DROP TABLE #TableRowCounts	

Note:-The above query is expensive. Please use at your risk

Output-

TableCount-spMSforeachdb&table

The use of IF and Like clause with sp_MSforeachtable

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' =''[dbo].[log]'')
BEGIN
 PRINT N''?''
 SELECT COUNT(*) from ? 
END'

EXEC [sp_MSforeachtable] @command1=N'
IF (N''?'' LIKE N''%Resource%]'')
BEGIN
 PRINT N''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ? 
END
'

ms_foreachtable1

The use of @whereand parameter to filter any object with sp_MSforeachtable

EXEC sp_msforeachtable
 @command1 ='SELECT ''?'',count(*) FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%EMPLOYEE%'')'

OR

EXEC [sp_MSforeachtable]@command1=N'
 PRINT N''?''
 SELECT ''?'',COUNT(*) FROM ?',
 @whereand = N'AND o.[name] LIKE N''%EMPLOYEE%'''

ms_foreachtable2

Example to rebuild ALL indexes of ALL tables of given databases using sp_MSforachdb and sp_MSforachtable

EXEC sp_msforeachdb @command1='
use };
if ''}'' in (''Employee'', ''CellLevelEncryptionDemo'')
begin
print ''}''
exec sp_MSforeachtable @command1=''
ALTER INDEX all ON ? rebuild WITH (FILLFACTOR = 90, sort_in_tempdb = ON);
''
end 
', @replacechar = '}'
Posted in sp_MSforeachtable, SQL, T-SQL | Tagged , , , , , , , , , , | 1 Comment

SQL- Querying XML attributes from XML Column

Read XML column attributes in to two columns DimType and Dimvalue  from the below example also retrieve the data matching values[DimTypes (WIDTH or Height)].

‘<dimensions>

<dimension name=”width”  value=”12.77″/>

<dimension name=”height”  value=”0.14″/>

<dimension name=”depth”      value=”12.92″/>

</dimenstions>’

Download SQL :-  https://gallery.technet.microsoft.com/SQL-Querying-XML-f700bbb0

 SQL:-

DECLARE @xml XML
SELECT @xml = 
'<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
</dimensions>'

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
FROM    @xml.nodes('/dimensions/dimension') x(v)

second1

create table #demo (field1 xml)
insert into #demo (field1) 
values ('<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
</dimensions>')

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension') x(v)

first1

Select data for DimType “height and Width”

DECLARE @xml XML
SELECT @xml = 
'<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
   <dimension name="depth"   value="12.92"/>
</dimensions>'

SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
FROM    @xml.nodes('/dimensions/dimension[@name = "height" or @name = "width"]') x(v)

third1

Using Temp table and Join –

create table #demo (field1 xml)
insert into #demo (field1) 
values ('<dimensions>
 <dimension name="height" value="0.14" /> 
 <dimension name="width" value="12.77"/> 
</dimensions>')

SELECT x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
 x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension[@name = "height" or @name = "width"]') x(v)

fourth1

Using SQL Where Clause to fetch dimension type data ‘HEIGHT’ or ‘WIDTH’

CREATE TABLE #demo (field1 xml)
INSERT INTO #demo (field1) 
values ('<dimensions>
  <dimension name="height" value="0.14" /> 
  <dimension name="width"  value="12.77"/> 
  <dimension name="depth"   value="12.92"/>
</dimensions>')

select * from #demo

select 
dimtype,
dimvalue
FROM
(
SELECT  x.v.value('@name[1]', 'VARCHAR(100)') AS dimtype ,
        x.v.value('@value[1]', 'VARCHAR(100)') AS dimvalue
from #demo
cross apply field1.nodes('/dimensions/dimension') x(v)
)T
where
T.dimtype in('height','width')
Posted in SQL XML | Tagged , , , | Leave a comment

T-SQL to Display Weekends Between two Dates

Different ways to find the weekend between two given dates. The SQL requires @begindate and @endate paramteters to be entered in the below SQL

Download T-SQL WeekendBetweenTwoValidDates

DECLARE @beginDate Date='20150101',
		@endDate Date='20150131'


DECLARE @Calendar Table
(CalendarDate Date Primary key, IsWeekend Bit)

WHILE @beginDate <= @endDate
BEGIN
INSERT INTO @Calendar
SELECT
@beginDate As CalendarDate
,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
Set @beginDate = DateAdd(Day, 1, @beginDate)
End

SELECT CalendarDate From @Calendar Where IsWeekend = 1

On SQL 2012 or higher version you can use choose function.

http://msdn.microsoft.com/en-us/library/hh213019.aspx

DECLARE @beginDate Date='20150101',
 @EndDate Date='20150131'


Declare @Calendar Table
(CalendarDate Date Primary key, IsWeekend varchar(20))

While @beginDate <= @endDate
Begin
Insert Into @Calendar
SELECT 
@beginDate As CalendarDate,
CHOOSE(DATEPART(dw, @beginDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') 
Set @beginDate = DateAdd(Day, 1, @beginDate) 
End

Select CalendarDate From @Calendar where IsWeekend='WEEKEND'

Using Number table

Declare @beginDate date = '20150101', @endDate date = '20150131'

SELECT 
DATEADD(dd, Number, @beginDate)
from
(
SELECT
 (a.Number * 256) + b.Number AS Number
FROM 
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) a (Number),
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) b (Number)
)T
Where
Number >= 0
and DATEADD(dd, Number, @beginDate) <= @endDate
and DATEPART(dw, DATEADD(dd, Number, @beginDate)) in ( 1, 7 )

Ouput:-

Weekend_Between_Two_Dates

Posted in T-SQL | Tagged , , , , , , , , | 2 Comments

T-SQL – How to find Next Business day from a given date

There are many methods to find next business day. One common way is to use of calendar table.  This post is to get an output using T-SQL Or UDF function.

Download T-SQL  NextBusinessDay

The details are given below

The script can be executed by feeding Input values to SQL. In the below example @dt parameter accepts input value.

DECLARE @dt datetime='20150113'
SELECT DATEADD(dd,CASE WHEN DATEDIFF(dd,0,@dt)%7 > 3 THEN 7-DATEDIFF(dd,0,@dt)%7 ELSE 1 END,@dt)

NextWorkingDay1

The following methods the input is fed from Table.

Method 1: T-SQL

Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');

Select MyDate, DateAdd(day, Case DateDiff(day, '19000101', MyDate) % 7 When 4 Then 3 When 5 Then 2 Else 1 End, MyDate) As NextWeekDay
From @TestDate;

NextWorkingDay2

Method 2: T-SQL

Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');

SELECT MyDate,DATEADD(dd,CASE WHEN DATEDIFF(dd,0,MyDate)%7 > 3 THEN 7-DATEDIFF(dd,0,MyDate)%7 ELSE 1 END,MyDate) from @TestDate

NextWorkingDay3

Method 3 : UDF

CREATE FUNCTION dbo.UF_GetWorkingDay(@givenDate DATETIME)
RETURNS DATETIME
AS
BEGIN
 DECLARE @workingDate DATETIME
 IF (DATENAME(dw , @givenDate) = 'Friday')
 BEGIN
 SET @workingDate = DATEADD(day, 3, @givenDate)
 END
 ELSE IF (DATENAME(dw , @givenDate) = 'Saturday')
 BEGIN
 SET @workingDate = DATEADD(day, 2, @givenDate)
 END
 ELSE 
 BEGIN
 SET @workingDate = DATEADD(day, 1, @givenDate)
 END
 RETURN @workingDate
END

Declare @TestDate Table(MyDate date);
Insert @TestDate(MyDate) Values('20150112'), ('20150113'), ('20150114'), ('20150115'), ('20150116'), ('20150117'), ('20150118');
select MyDate,dbo.UF_GetWorkingDay(Mydate) from @TestDate

NextWorkingDay4

Posted in T-SQL | Tagged , , , , , , , | 1 Comment