John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

John McCormack's blogs

List all views

1st August 2014 By John McCormack Leave a Comment

This script is handy when you want to list all views on an instance or even find a specific view if you know the name but don’t know the database.

[sql]

— Written by John McCormack

— This script uses the unspported stored procedure sp_msforeachdb so there are more reliable ways of going about this task.
— It uses a table variable and inserts each row returned into the table variable
— The ? is the database – All databases will be

DECLARE @views table (DBName sysname, ViewName nvarchar(100))
INSERT INTO @views
EXEC sp_msforeachdb
‘USE ?
SELECT ”?”,name FROM sys.views
— where [name] = ”vw_CurrentDaySales”’
— Uncomment the where clause if you wanted to find a specific view

SELECT * FROM @views

[/sql]

Filed Under: T-SQL Tagged With: find views, sp_msforeachdb, sql server views

Check percentage complete of transaction

30th July 2014 By John McCormack Leave a Comment

SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE session_id = 63 -- Remember to change session_id = to the relevant spid

/*

This simple script shows what percentage of a transaction has completed
This works for the following types of transaction:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE,
ROLLBACK
TDE ENCRYPTION
Change the 63 in the where clause to match the spid for your transaction.
Hint exec sp_who2 and look at command. This is the spid you want.

*/

Filed Under: front-page, T-SQL Tagged With: DBCC, DBCC SHRINKFILE, DbccFilesCompact, percent complete, spid, sys.dm_exec_requests

  • « Previous Page
  • 1
  • …
  • 19
  • 20
  • 21
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023