Search

Advertisements

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


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


See Also:

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'



See Also:


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%'


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


See Also:

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


See Also:

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) 


See Also:

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)


See Also:

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

Below script using system table in sql server can be used to get list of Stored procedure and function that references a particular table.

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

See Also: 


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)


See Also:

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

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


See Also:



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

Visual Basic .NET

Visual Basic .NET Programming for Beginners


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

Advertisements