Category Archives: Scripts

Generate Permission script for an object or all object

In the DBA world it’s necessary to pickup permissions for an object for all objects when he\she deploys the code. During deployment if the code has DROP and CREATE then it’s obvious that what ever permission you have assigned to that object will get lost so as a first step you need to script out permissions for the objects. You can get similar information using sp_helprotect procedure but you need to manually write the grant script from the output and you can’t use it for multiple or all objects. This is where I felt to write a script which can fetch all the permission related information for an object or for all the objects using sys.database_permissions dmv.

Continue reading Generate Permission script for an object or all object

Batch script to deploy multiple SQL files

I was working on a project where I’m supposed to deploy lots of SQL Server script files (.sql). It’s very tedious job either to manually execute or to write a batch script each time when the deployment moves to another environment. So I decided to write a batch script which should be dynamic in nature so that I can easily use this script across any SQL Script files.

Continue reading Batch script to deploy multiple SQL files

Find Indexed column details in a database

I was looking for a script to find out the indexed column details as part of our migration. I tried to use sp_helpindex however it requires a object name which means you can find details for only one table at a time. My requirement is to get details about all indexed columns across the database so I came up with this script. Let me know your views.

Continue reading Find Indexed column details in a database

Script to get data file usage and autogrowth details

I’ve wrote this script to get data file allocated and used space. In addition to this I’ve also added auto growth setting details for the data file. Written two scripts for fetching the details, first one will get you the details for one database and second one will give you the details for all the database data files. Continue reading Script to get data file usage and autogrowth details