SELECT Object_name(object_id) as [Procedure Name], Object_definition(object_id) as [Definition] FROM sys.procedures WHERE Object_definition(object_id) LIKE '%cursor%' Order by [Procedure Name]
First, you will need to enable xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.
Next, you will need to change the path to the directories:
USE Master; GO SET NOCOUNT ON -- 1 - Variable declaration DECLARE @backupPath NVARCHAR(500) DECLARE @cmd NVARCHAR(500) DECLARE @fileList TABLE (backupFile NVARCHAR(255)) -- 2 - Initialize variables SET @backupPath = 'F:\baks\' -- 3 - get list of files SET @cmd = 'DIR /b "' + @backupPath + '"' INSERT INTO @fileList(backupFile) EXEC master.sys.xp_cmdshell @cmd SELECT 'RESTORE DATABASE [' + LEFT(backupFile, CHARINDEX('_', backupFile, 12)-1) + '] FROM DISK = ''' + @backupPath + backupFile + ''' WITH RECOVERY, REPLACE GO' from @fileList
Copy the generated script into a new window and voila! Your script is done. Just press F5 to run it.
Don’t forget to turn off xp_cmdshell when done.
There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.
Imagine the following table:
ABC ABC1 ABC2 ABC3 and so on... ABC11 ABC12 ABC13 and so on.. ABC20 ABC21 ABC22 and so on..
So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.
When you run
select * from table order by my column asc
you get following results:
ABC ABC1 ABC11 ABC12 ABC13 ABC2 ABC20 ABC21 ABC22 ABC3 ABC31 ABC32
In this scenario, you would need the results sorted by the numeric value and not alphabetically.
ABC ABC1 ABC2 ABC3 ABC11 ABC12 ABC13 ABC20 ABC21 ABC22 ABC31 ABC32
How can this be accomplished?
We have been experiencing issues with some massive viewstates causing slow page loads and “System.FormatException: Invalid length for a Base-64 char array or string”.
Imagine you have this scenario – the order of a table which relies on its order has been muddled up by some bad code and you wish to sort it out with the least effort possible.
You can write a quick SQL script which will reset the order to the right set (seen on the right).
To find out what your order should be, include the ROW_NUMBER() command as below:
,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row
from DM_ReportColumns where [conditions]
Then you can run an update based on your primary keys (in this scenario, I have ReportID, Username and ColumnID as keys.
UPDATE DM_ReportColumns SET Orders = x.Row from ( select * ,ROW_NUMBER() OVER(ORDER BY Orders ASC) AS Row from DM_ReportColumns where ReportID = 'A01' and Username = 'user1' ) as x WHERE x.ReportID = DM_ReportColumns.ReportID AND x.Username = DM_ReportColumns.Username AND x.ColumnID = DM_ReportColumns.ColumnID
In some cases, to improve performance, you might choose to re-compile all database objects and while you can use DBCC FREEPROCCACHE to clear the cache and force re-compilation, you can also use the following function to trigger a global re-compile.
This is especially useful if you are struggling to copy databases and old code error does not let you skip steps. Continue reading “Script to recompile all stored procedures – T-SQL”
SELECT ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' + I.name + '] ON ' + Schema_name(T.Schema_id)+'.'+T.name + ' ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + -- default value --' DROP_EXISTING = ON ' + ',' + -- default value ' ONLINE = OFF ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON [' + DS.name + ' ] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , [' + C.name + ']' FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 WHERE IC2.Object_id = object_id('tblERPInvoiceData') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables --AND I.name = 'IX_Address_PostalCode' --comment for all indexes