sp_MSforeachtable @command1="print '?'", @command2="SELECT COUNT(*) AS '?' FROM ?"
TSQL Script - To find no. of Rows in all tables of a database
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
Subscribe to:
Posts (Atom)