Search

Advertisements

TSQL Script - To find no. of Rows in all tables of a database

sp_MSforeachtable @command1="print '?'", @command2="SELECT COUNT(*) AS '?' FROM ?"

TSQL Script- To find tables without primary key & heap tables

SELECT  S.name+'.'+T.name AS Tablename 
FROM sys.tables T 
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id 
WHERE T.type = 'U' 
AND NOT EXISTS 
SELECT C.name 
FROM  sys.key_constraints C 
WHERE  C.parent_object_id = T.object_id 
AND  C.schema_id = S.schema_id 
AND  C.type = 'PK')

-------------------------------------------------------------------------------

SELECT
 T.name Heap_Tables FROM SYS.TABLES T
INNER JOIN SYS.INDEXES I
ON T.object_id = I.object_id
AND I.type_desc='HEAP'


 




TSQL Script - To change Primary key names to standard naming convention across the DB

SELECT 'exec sp_rename '+''''+'['+S.name+'].['+b.name+']'+''''+','+''''+'PK_'+a.name+''''+','+''''+'OBJECT'+''''+CHAR(10)+'GO'
FROM sys.objects a
INNER JOIN  sys.indexes b ON a.object_id=b.object_id
INNER JOIN sys.schemas S ON a.schema_id=S.schema_id
WHERE a.object_id =b.object_id AND (b.is_primary_key=1 )
AND a.type='U' AND a.name not like 'sys%'

Sqlserver - Different ways to find blocking

  • sp_who2 System Stored Procedure
  • sys.dm_exec_requests DMV
  • Sys.dm_os_waiting_tasks
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler

T SQL script- to get tables with no clustered indexes

SELECT *  FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0

T-SQL script - Find Currently Running Query

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 




Reference : Pinal Dave (http://blog.SQLAuthority.com) 

TSQL script - Find Highest / Most Used Stored Procedure

SELECT TOP 10 qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = 'AdventureWorks')
ORDER BY qs.total_physical_reads DESC



Reference : Pinal Dave (http://blog.SQLAuthority.com)

T-SQL script - Find Stored Procedure & Functions Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT SO.name, SO.xtype
FROM syscomments SC
JOIN sysobjects SO ON SC.id=SO.id
WHERE SC.TEXT LIKE '%tablename%'

T-SQL script -List columns and attributes for every table in a SQL Server database


 SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema], 
                                         T.[name] AS [table_name], AC.[name] AS [column_name], 
                                         TY.[name] AS system_data_type, AC.[max_length],
                                         AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded] 
 FROM sys.[tables] AS T 
 INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
 INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
                                              AND AC.[user_type_id] = TY.[user_type_id]  
 WHERE T.[is_ms_shipped] = 0 
 ORDER BY T.[name], AC.[column_id]



Reference : Pinal Dave (http://blog.SQLAuthority.com)

T-SQL script - Backup History Analysis

SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'BackupReport'
ORDER BY database_name, backup_start_date, backup_finish_date
 

T-SQL script to list object dependencies

-- SQL Server 2008 object dependency query - listing object dependencies
SELECT ReferencingObjectType = o1.type,
       ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id)+'.'+ed.referenced_entity_name,
       ReferencedObjectType = o2.type
FROM   sys.sql_expression_dependencies ed
       INNER JOIN   sys.objects o1
         ON ed.referencing_id = o1.object_id
       INNER JOIN sys.objects o2
         ON ed.referenced_id = o2.object_id
WHERE o1.type in ('P','TR','V', 'TF')
ORDER BY ReferencingObjectType, ReferencingObject


Reference : http://msdn.microsoft.com/en-us/library/ms345449(v=sql.105).aspx
                         

Visual Basic .NET

Visual Basic .NET Programming for Beginners


http://www.homeandlearn.co.uk/net/vbNet.html

Advertisements