sp_msforeachdb: Shrinking log files for all databases in SQL Server

This post is mainly to answer a question in one of my earlier post https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/

Question:

Mr Yagnesh was looking for a script to shrink log file for all of his databases using sp_msforeachdb.

To answer the question, Yes, we can very well modify the script in the post to shrink the log files. However, SHRINKFILE file is not advisable in all situations, I would suggest to take that option with extreme case.

You may find the below script to *ONLY* generate the SHRINKFILE DBCC commands for all databases except system databases. The reason for generating the DBCC commands is to verify the commands before you execute in your environment as a first level of verification to avoid any surprises.

Script:

 --To avoid/exception on applying the change, you may add your databases in the list. 
EXEC sp_msforeachdb 'Declare @logname varchar(500) 
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
        set @logname = (Select name From Sys.master_files where DB_ID(''?'') = database_id and type=1) 
        print ''Use [?];'' 
        print ''DBCC SHRINKFILE (['' + @logname + ''] ,100)'' 
end 
' 

Alternatively, there is very simple method as below:


Select 'Use [' + db_name(database_id) + '];DBCC SHRINKFILE(['+ name + '],100)' 
	From sys.master_files where type=1
		and db_name(database_id) not in ('tempdb','master','msdb','model','Reportserver','ReportserverTempDB')

EDIT: 19th May 2017

One of my friend had difficulty to change the above to a dynamic execution statement. The below is my try to help those find it difficult:



EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
		DBCC SHRINKFILE (['' + @logname + ''] ,100)'')  
end 
' 

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48
https://latheeshnk.wordpress.com/2015/05/06/identify-references-of-an-object-in-sql-server/
https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/

Advertisements

How to find Primary Key and Key columns for tables in SQL Server

Its a very regular question in many of the forums “How to identify the primary key and its key column information for tables in SQL Server“.

Method 1:

1. Select the table in SSMS – (Query window)
2. Press – ALT-F1

The limitation is that, the above method can be useful only for a single table. If you are looking for a list of tables, then you can refer the second method.

Method 2:

T-SQL Script to identify the Primary key and Key column information for all tables.


;WITH CTE 
AS
(
	SELECT A.TABLE_SCHEMA,A.TABLE_NAME,A.CONSTRAINT_NAME,B.COLUMN_NAME,B.ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME 
			AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
	WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
		--AND A.TABLE_NAME LIKE 'Tablename%' -- To filter for a particular table/name starts with
)
SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE A
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
					ORDER BY A.ORDINAL_POSITION
					FOR XML PATH ('') )
					, ' ', ',')
FROM CTE B

References:
You may refer the below link to get more information for a table.
Handy Table Info Script

sp_refreshview: Some of interesting facts on its usage

Introduction to sp_refreshview?

sp_refreshview is a system procedure used to update the metadata for the specified view those are not schema bounded.
The definition of sp_refreshview is as below:


create procedure sys.sp_refreshview  
    @viewname nvarchar(776)  
as  
 declare @ret int  
 exec @ret = sys.sp_refreshsqlmodule_internal @viewname, N'OBJECT', 1 -- viewonly  
 return @ret 

Why do we need sp_refreshview?

If your underlying object (table/view) has a modification( we will look into the type of modification sometime later), the metadata of the view needs to be updated to take the effect of the changes unless the VIEW has been sepcified with SCHEMABINDING. To update the metadata, we need to either recreate the view(drop+create) OR need to refresh the view with sp_refreshview system procedure.

Lets look at an example.


--create a Base Table
Create Table ParentTable(EmployeeID int, EmployeeName Varchar(50))

Insert into ParentTable Values(1,'Ben'),(2,'Tom')

--create a View

Create View ParentView
as
	Select * From ParentTable
	
Select * From ParentView	

--Make a modification on Table schema
Alter table ParentTable add EmployeeAge int

Select * From ParentView	

Here, we can see that the EmployeeAge is not coming as part of the view as the metadata has not been updated though the table has the column.To update the metadata, we are going to refresh the view using sp_refreshview.


sp_refreshview 'ParentView'

Select * From ParentView	

Now, we can see the EmployeeAge is part of the View ParentView. We are good…

Interesting part…Do we need to refresh view for all modification?

No. Not always. It depends on your underlying object type. If you have a table as an underlying object, yes, you may need to do refresh view for the modifications.But, if the underlying object is again another VIEW, then we may not do a refresh for all cases.

Lets look at an example:

To make the example easier, We are going to create another view CHILDVIEW on PARENTVIEW as underlying object.


Create View ChildView
as
	Select * From ParentView
	
Select * From ChildView	

Let us do a modification on the ParentView by adding a Union All as below.


Alter View ParentView
as
	Select * From ParentTable
	Union All
	Select * From ParentTable

Select * From ChildView	

Here, we can observe that even there is a change in the ParentView,ChildView works as expected without a refresh.

Below, I compiled a small table for various types of changes. Of course,the table does not cover all scenarios. You can test for any specific case and confirm further.

sp_refreshview_Usage

Hope, you enjoy this post, please have your thoughts as part of our learning and sharing…..