Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.
If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.
The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.
Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.
If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.
Here’s a quick script to set a database to single user and then back to multi user.
You’ll need to do this when altering the database in situations like database name changes, file name changes or even restores
This posting is particularly useful for anyone looking to use the date a web page was published as part of academic referencing.
Step 1. In your browser open https://www.google.com/.
Step 2. Copy the url of the web page you want to get a publication date for.
Step 3. Paste this url into the search bar of the google.com page and search. The search should return a series of results.
Step 4. In the address bar of the results page go to the very end of the url text and paste &as_qdr=y15 and hit return.
This should update the results page with each link including a publication date.
This script is for SQL Server 2005 and up. The script will return all the missing indexes for a SQL Server instance, rating their impact and provide the T-SQL to create the missing indexes.
SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) and DMO (Database Management Objects) which this script requires to function.
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)
You need to provide the following at the start of the script:
- The target database i.e. the database you will restore to
- The directory where the backup file is saved
If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.
This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.
The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.
Some use cases for this solution might be:
- Restoring a nightly backup to another instance for reporting purposes
- Restoring backups to a development environment
- Restoring backups to another server to test the backups
You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.
An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.
For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.
Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.
The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.
- Open Microsoft SQL Server Management Studio (SSMS).
- Connect to the server that houses the Db you are working with.
- Run the modified script
- Right click on the Db in SSMS and select Tasks > Take Offline
- If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
- Go back to SSMS and right click on the Db and select Tasks > Bring Online.
- If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
- Now you can rename the Db to the new name if you wish using SSMS.
If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.
The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.
This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.
Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.
- status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
- blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use
EXEC sp_who2to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
KILL blocking_session_id /*replace by the actual Id*/
NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.
- wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
- wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
- last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.
This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)
Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.
With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.
To do this you can just download the Excel file template here. Download
The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.
A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)
To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.
You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.
Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.
You can create the Excel file manually yourself without downloading it.
To do so open a new Excel file and in an empty sheet name the first 9 columns as below:
For D2 enter the following:
=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”
For E2 enter the following:
=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”
For F2 enter the following:
=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”
To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.
The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.