Category Archives: T-SQL Scripts

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’

User ‘public’ does not have permission to run DBCC showfilestats for database ‘ testing’ This error message is raised when your user account does not have the db_owner role of a database meaning you are not the owner of the … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks, Troubleshooting | Tagged , , | Leave a comment

Check SQL Wait Types to identify potential problems on sql servers

Production DBAs often end firefighting issues on live servers and this can easily side track the DBA’s from the actual problems underlying a Database or at sql server instance level. This is where my Sproc will come in handy.  I have … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks, WaitTypes | Tagged , | 2 Comments

T-sql to get restore history of SQL databases

Run the script on database instance where you need to find the restore history of all the databases. This provides 4 column result set of Which database has been restored (DBRestored) When was it restore (RestoreDate) What is the source … Continue reading

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

T-SQL script to list Page Verify Option of all user Databases in sql server 2008/2005

— Script to list All user databases and their page verify option set. SELECT  name AS [Database] , CASE WHEN page_verify_option = 0 THEN ‘NONE’ WHEN page_verify_option = 1 THEN ‘TORN_PAGE_DETECTION’ WHEN page_verify_option = 2 THEN ‘CHECKSUM’ END AS [Page Verify] … Continue reading

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

How to run multiple SQL scripts

Scenario 1:- Create a .bat file with the below code and store the .bat file in the folder containing all the .sql scripts. setlocal SET SQLCMD=sqlcmd -S<servername\instancename> -d<database name> -E for %%d in (*.sql) do %SQLCMD% -i%%d endlocal Double click … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment

Execute SQL script from command prompt

We are often required to run certain scripts or deploy scripts to sql server using command prompt; here how we can do it seemlessly. Open command prompt and type in the below sqlcmd mentioning the relevant servername /database name and … Continue reading

Posted in SQL Server, T-SQL Scripts, Tricks | Tagged , | Leave a comment

SQL Script to add and Remove Database role members

/*========================================================== Author: Suleman Mohammed Description: Scripts out statements to add & drop Database role members for each of the Database roles, ordered by Database role name and Member name ===========================================================*/ SELECT [Database Role] = r.name , [Member] = m.name , … Continue reading

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

SQL Script to add and Remove SQL server role members

/*========================================================= Author: Suleman Mohammed Description: Scripts out statements to add & drop SQL server role members for each of the SQL server roles, ordered by Server role name and Member name ==========================================================*/ SELECT [Server Role] = r.name ,[Member] = m.name … Continue reading

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