sp_MSforeachtable Stored Procedure

sp_MSforeachtable stored procedure provided by Microsoft, is used for smooth processing on all tables of a single database. So why to use complex query when we have already that procedure.

How and when to use sp_MSFooreachtable :

Parameters

sp_MSforeachtable @command1, @replacechar, @command2,  @command3, @whereand, @precommand, @postcommand

• @command1 nvarchar(2000) – is the first command to be executed
• @replacechar nchar(1) – is a character in the command string that will be replaced with the table name being processed (default character is “?”)
• @command2 nvarchar(2000)-  additional command that can be run for each table. It runs after @ command1
• @command2 nvarchar(2000)-  another additional command that can be run for each table. It runs after @ comman2
• @whereand nvarchar(2000)- you can specify the where clause in this parameter
• @precommand nvarchar(2000) -  specifies a command to be run prior to processing any table
• @postcommand nvarchar(2000)- command to be executed  after all commands have been processed

Eg 1 : Retriving all tables starting with 'c' and their row count CREATE TABLE  #rowcount (tablename varchar(128), cnt int) EXEC sp_MSforeachtable  @command1 = 'Insert into #rowcount select ''?'',               count(*) from ?',  @whereand = 'and o.name like ''p%''',  @postcommand = 'SELECT * from #rowcount' DROP TABLE  #rowcount Eg 2:  Disabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Eg 3:  Enabling all constraints on all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Eg 4:  Deleting rows from all tables

EXEC sp_MSForEachTable 'DELETE FROM ?'

Eg 5:  Reseed identity column

If any table doesn't contain identity column, it raise error and as a quality of stored procedure it will move on EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)' Eg 6:   Rebuild clustered index/all non clusted indexes on all the tables EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)'

Removing pre zero from number :

select substring(Column, patindex('%[^0]%',Column), 10) from Table

Find only characters or only integer from alphanumeric value :

Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

====================================

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

=====================================

Connection Property : Port, IP .....

To get locl tcp port and ip address:   

select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address     is not null

To get all information related to sql connections:   

 select * from sys.dm_exec_connections


=============================================

Query Connection Properties :

SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

=============================================

When connecting locally,

Transport Protocol always comes “SHARED MEMORY” and TCP Port as NULL.

When connecting remotely,

Transport Protocol always comes “TCP” and TCP Port as negative number (if the port is set to dynamic port).

To get the actual port, just do a subtraction from a number “65536” and we get the actual port number.

Sql Installation

Code to check Sql Server Installation Date & Time :

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE 'INSTALALTIONDATE'

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

Code For Truncating Table with foreign Key Constraints:

Use <DATABASE>
Go
/*
Created By Vimal Lohani
*/
Declare @data nvarchar(max)
-- RECREATE CONSTRAINTS
SELECT ROW_NUMBER() over(order by f.object_id) 'rno', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
into #tempcreate
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

-- DROP CONSTRAINTS table
SELECT ROW_NUMBER() over(order by f.object_id) 'rowno','ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']' as dropscript
into #tempdrop
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
--select * from #tempdrop
--Drop Constraints process
declare @max int=(Select max(rowno) from #tempdrop)
while(@max>0)
Begin
Select  @data=dropscript from #tempdrop where rowno=@max
Exec(@data)
--PRINT @data
Set @max=@max-1
End

--Type your all Truncate Code Here

truncate table [dbo].[fact_Employee]



--Complete Truncate Code


--Create Constraints Process
declare @maxx int=(Select max(rno) from #tempcreate)
while(@maxx>0)
Begin
Select  @data=Scripts from #tempcreate where rno=@maxx
Exec(@data)
--PRINT @data
Set @maxx=@maxx-1
End

drop table #tempdrop
drop table #tempcreate

Sql Logs :

Backup Log Code :

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--where database_name like '<dbname>'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date asc