Epoch to SQL Date Time
Below is the query which can be used to search for text ( for example a table) SELECT DISTINCT o.name AS Object_Name FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like ‘%Tablename%’– Table name here and o.type_desc =’SQL_STORED_PROCEDURE’ Let me know if you want to know moreRead more "Search for text in SP’s Defination"
You may come across a scenario where you need to extract images from your database to a drive, below script can be used: DECLARE CURSOR_ProductIds CURSOR FOR ( select top 10 iDocID from ScannedDocs) DECLARE @ProductId INT; OPEN CURSOR_ProductIds FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId WHILE (@@FETCH_STATUS <> -1) BEGIN DECLARE @ImageData varbinary(max); SELECT @ImageData […]Read more "Extract Images from A database"
A Column which would generally have NULL values can made a sparse column which effectively uses no space for NULLs, Hence effective storage. CREATE TABLE Person ( ID INT PRIMARY KEY, Email VARCHAR(100) SPARSE NULL ) GO Thing to note is that the sparse column has to be NULLABLE A filtered index is an index […]Read more "Sparse Column"
Nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in […]Read more "Filtered Index"
Suppose you need to work on a database where the task at hand requires you to delete some data but the data is not tightly coupled ( Reference Keys ), but you need to be sure that you may be able to hit as many columns as you can. However this is all about luck. […]Read more "Search for loosely coupled column in Sql Server"
Below Script will help you check who updated the table : SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( ‘DATABASE’) AND OBJECT_ID=OBJECT_ID(‘TABLE’)Read more "Last Update on table"
Supose you need to check the table size along with the rows it has , Following Query will help you do it : SELECT NAME, ‘ SELECT @@SERVERNAME AS SERVER, ”’+NAME+”’ [Database Name], t.Name AS TableName, p.rows AS RowCounts, CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB, CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS […]Read more "Table Size in all databases"