Category: sql

The following are a list of good practices I follow when querying large sets of data. SELECT * Avoid selecting all columns from a table. Queries should explicitly list each column it requires Changing column definitions on the underlying tables can have an adverse effect on dependencies such as views, name clashes etc Avoid reading and returning unnecessary data Can… Read more →

DECLARE tableCursor CURSOR LOCAL FOR SELECT name, ‘Foundation’, name + ‘Dto’ FROM sys.tables WHERE 1 = 1 OPEN tableCursor DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX) FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className WHILE @@FETCH_STATUS = 0 BEGIN DECLARE tableColumns CURSOR LOCAL FOR SELECT cols.name, cols.system_type_id, cols.is_nullable FROM sys.columns cols JOIN sys.tables tbl ON cols.object_id = tbl.object_id WHERE tbl.name… Read more →

Easy with Simple Recovery DBCC SHRINKFILE (“<FileName>_Log”, 1); GO otherwise http://social.technet.microsoft.com/Forums/sqlserver/en-US/0a7a7fc5-d30e-4841-8ed1-84676b575e55/sql-server-2012-how-to-shrink-the-database-transaction-log USE AdventureWorks2008R2; GO — Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE; GO — Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1); GO — Reset the database recovery model. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL; GO Read more →

http://www.sqlservercentral.com/scripts/Backup/62380/ Backup Integrity check Index & statistics maintenance Read more →

SELECT sysobjects.Name , sysindexes.Rows FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE type = ‘U’ AND sysindexes.IndId < 2 ORDER BY sysobjects.Namecode Read more →