Category: SCRIPT GALLERY

SCRIPT GALLERY, SQL August 20, 2015September 20, 2020

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/

SCRIPT GALLERY, SQL August 19, 2015September 20, 2020

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

SCRIPT GALLERY, SQL July 3, 2015September 20, 2020

How to identify User Defined Table(UDT) Type Information in SQL Server

Here is a script to identify the User Defined Table Type information.

The script will list down the information like column name, type name and Schema name.


SELECT 
	TT.NAME TABLE_NAME, C.NAME COLUMN_NAME 
        ,TYPE_NAME(TT.SYSTEM_TYPE_ID) [TYPE_NAME]
	,SCHEMA_NAME(SCHEMA_ID) [SCHEMA_NAME]
FROM SYS.TABLE_TYPES TT
INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = TT.TYPE_TABLE_OBJECT_ID
ORDER BY TT.NAME

SCRIPT GALLERY, SQL June 2, 2015September 20, 2020

sp_msforeachdb: Changing recovery model for all databases

Introduction:

Here is another script that used sp_msforeachdb to change the recovery model for all the databases.

Note: There is a caveat that changing the recovery model is not recommended without thorough knowledge, but here we are only discussing the technical aspect.


Select name,recovery_model_desc From Sys.databases

--To avoid/exception on applying the change, you may add your databases in the list.
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'')
begin
    exec (''ALTER DATABASE [?] SET RECOVERY SIMPLE;'')
end
'

Select name,recovery_model_desc From Sys.databases