On this page, SQLNetHub members can find useful T-SQL scripts that can help them perform different tasks in SQL Server.
[ihc-hide-content ihc_mb_type=”block” ihc_mb_who=”unreg” ihc_mb_template=”3″ ]
SQL Server Administration
Rebuild all Indexes Online for all Tables:
USE [DATABASE_NAME]; GO EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)"; GO
Get Useful SQL Server Information:
SELECT SERVERPROPERTY('ServerName') AS FullInstanceName, REPLACE(SUBSTRING(@@version,0,CHARINDEX('-',@@version)),'Microsoft ','') as FullSQLVersion, SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS, SERVERPROPERTY('MachineName') AS MachineName, SERVERPROPERTY('InstanceName') as InstanceName, SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion, SERVERPROPERTY('Collation') AS Collation, SERVERPROPERTY ('edition') as InstanceEdition, CASE WHEN SERVERPROPERTY('EngineEdition')=1 THEN 'Personal/Desktop' WHEN SERVERPROPERTY('EngineEdition')=2 THEN 'Standard' WHEN SERVERPROPERTY('EngineEdition')=3 THEN 'Enterprise' WHEN SERVERPROPERTY('EngineEdition')=4 THEN 'Express' WHEN SERVERPROPERTY('EngineEdition')=5 THEN 'SQL Database' WHEN SERVERPROPERTY('EngineEdition')=6 THEN 'SQL Data Warehouse' END AS EngineEdition, CASE WHEN SERVERPROPERTY('IsClustered')=1 THEN 'Clustered' WHEN SERVERPROPERTY('IsClustered')=0 THEN 'Not Clustered' ELSE 'N/A' END AS ClusteredStatus, (SELECT COUNT(*) FROM sys.databases) AS TotalDatabases
Check Database Isolation Level:
USE [Database_Name]; GO DBCC USEROPTIONS; GO
Search for Keywords in SQL Server Agent Jobs:
declare @pattern nvarchar(max) set @pattern=’table2′ select j.[job_id],j.[name],j.[enabled],j.[description],s.command,j.[date_created],j.[date_modified] from msdb.dbo.sysjobs j inner join msdb.dbo.[sysjobsteps] s on j.job_id=s.job_id where s.command like '%'+@pattern+'%'
More tips are coming soon!
[/ihc-hide-content]