SELECT OBJECT_NAME(tbl.object_id),
'Index Name' = ind.name ,
'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
INNER JOIN SYS.TABLES tbl
ON tbl.OBJECT_ID = ind.object_id
WHERE tbl.TYPE = 'U'
--USE babu;
--GO
--EXEC sp_updatestats --To update all STATISTICS in the DB
Wednesday, December 2, 2009
Methods to find table INDEX or Statistics age for better performance
Original Post
Create Parameters
DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'
DECLARE @TableName AS VARCHAR(200)
DECLARE @ParamNames AS VARCHAR(8000)
SET @TableName = 'TableTest'
SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +
CASE
WHEN DATA_TYPE = 'bigint'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'binary'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'Bit'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'Char'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'Date'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'DateTime'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'DateTime2'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'DateTimeOffset'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'Decimal'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN DATA_TYPE = 'float'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
WHEN DATA_TYPE = 'geography'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'geometry'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'hierarchyid'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'image'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'int'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
WHEN DATA_TYPE = 'money'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN DATA_TYPE = 'NCHAR'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'numeric'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN DATA_TYPE = 'NVARCHAR'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'ntext'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'REAL'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
WHEN DATA_TYPE = 'smalldatetime'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'smallint'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
WHEN DATA_TYPE = 'smallmoney'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN DATA_TYPE = 'sql_variant'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'text'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'time'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'timestamp'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'tinyint'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
WHEN DATA_TYPE = 'uniqueidentifier'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
WHEN DATA_TYPE = 'varbinary'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'varchar'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN DATA_TYPE = 'xml'
THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
END ) + ','
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @TableName
SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)
PRINT @ParamNames
Re Written Query
DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',
@DATA_TYPE VARCHAR(50) = 'numeric'
SELECT paremeters = ('@' + column_name + ' ' +
CASE
WHEN data_type = 'BIGINT'
OR data_type = 'Bit'
OR data_type = 'Date'
OR data_type = 'DateTime'
OR data_type = 'DateTime2'
OR data_type = 'DateTimeOffset'
OR data_type = 'geography'
OR data_type = 'geometry'
OR data_type = 'hierarchyid'
OR data_type = 'image'
OR data_type = 'smalldatetime'
OR data_type = 'sql_variant'
OR data_type = 'time'
OR data_type = 'timestamp'
OR data_type = 'uniqueidentifier'
OR data_type = 'xml'
THEN data_type
WHEN data_type = 'binary'
OR data_type = 'Char'
OR data_type = 'NCHAR'
OR data_type = 'NVARCHAR'
OR data_type = 'text'
OR data_type = 'varbinary'
OR data_type = 'varchar'
THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN data_type = 'Decimal'
OR data_type = 'money'
OR data_type = 'numeric'
OR data_type = 'smallmoney'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN data_type = 'float'
OR data_type = 'int'
OR data_type = 'REAL'
OR data_type = 'smallint'
OR data_type = 'tinyint'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'
ELSE data_type
END) + ' ,'
FROM information_schema.columns
WHERE table_name = @TABLE_NAME
AND
(
CASE
WHEN
(
@DATA_TYPE IS NULL
OR @DATA_TYPE = ''
)
THEN 1
END = 1
OR data_type = @DATA_TYPE
)
Re Written Query
DECLARE @TableName AS VARCHAR(200)
DECLARE @TableName AS VARCHAR(200)
DECLARE @ParamNames AS VARCHAR(8000)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @NewLineChar AS CHAR(2)
SET @TableName = 'TableTest'
SET @HorizontalTabChar = CHAR(9)
SET @NewLineChar = CHAR(13) + CHAR(10)
SET @ParamNames = REPLICATE(@HorizontalTabChar, 3)
SELECT @ParamNames = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (
CASE
WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL
THEN '(' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'
ELSE '(' + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'
END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM sysobjects
JOIN syscolumns
ON sysobjects.id = syscolumns.id
INNER JOIN systypes t
ON syscolumns.xusertype = t.xusertype
WHERE sysobjects.ID = OBJECT_ID(@TableName)
SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-6)
PRINT @ParamNames
Another One Not Much Confident
DECLARE @TABLE_NAME VARCHAR(50)
SET @TABLE_NAME = 'tabletest'
SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +
CASE
WHEN syscolumns.prec IS NULL
AND syscolumns.scale IS NULL
THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
WHEN syscolumns.prec = 0
AND syscolumns.scale = 0
THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
WHEN syscolumns.prec > 0
AND syscolumns.scale = 0
THEN ''--CAST(syscolumns.length AS VARCHAR)
WHEN syscolumns.prec = 0
AND syscolumns.scale IS NULL
THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
WHEN syscolumns.prec > 0
AND syscolumns.scale IS NULL
THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
ELSE '(' + CAST(syscolumns.prec AS VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'
END
FROM sysobjects
JOIN syscolumns
ON sysobjects.id = syscolumns.id
JOIN systypes
ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype ='U'
AND sysobjects.name = @TABLE_NAME
Tuesday, November 17, 2009
Find the tables which does not have an Identity Key column
SELECT isc.table_name
FROM information_schema.columns AS isc
INNER JOIN sys.tables
ON SYS.tables.object_id = object_id(isc.TABLE_NAME)
WHERE SYS.tables.type = 'u'
GROUP BY isc.table_name
HAVING MAX(COLUMNPROPERTY( OBJECT_ID(isc.table_name), isc.column_name,'Isidentity')) =0--( To get the table which doesnot contains identity columns')
ORDER BY isc.table_name
Monday, November 16, 2009
Find the index of row in DataTable
string query = "Year = " + "2000" + " AND Period = " + "'1'";
int rowIndex = dtSaveAll.Rows.IndexOf(dtSaveAll.Select(query)(0));
int rowIndex = dtSaveAll.Rows.IndexOf(dtSaveAll.Select(query)(0));
Find all SQL Identity columns
SELECT '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
table_name ,
column_name ,
ordinal_position ,
data_type
FROM information_schema.columns
WHERE columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
ORDER BY table_name
SELECT '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
table_name ,
column_name ,
ordinal_position ,
data_type ,
Seed_Value AS SeedValue ,
Increment_Value AS IncrementValue ,
ident_current(Object_Name(ic.Object_ID)) AS CurrentValue ,
Last_Value AS LastValue
FROM information_schema.columns AS isc
INNER JOIN sys.identity_columns AS ic
ON ic.object_id = OBJECT_ID('['+isc.table_schema+'].['+isc.table_name+']')
INNER JOIN sys.tables
ON SYS.tables.object_id = ic.object_id
WHERE columnproperty(object_id(isc.table_schema + '.' + table_name), column_name,'IsIdentity') = 1 --( To get the table which contains only identity columns')
AND SYS.tables.type = 'u'
ORDER BY table_name
Thursday, November 5, 2009
Find table and index name for fragmented indexes
;
WITH GetFragmatation AS
( SELECT object_id AS ObjectID ,
OBJECT_NAME(object_id) AS TableName ,
index_id AS IndexID ,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags ,
avg_fragment_size_in_pages AS PagesPerFrag ,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('RKPMS'), NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 0
)
SELECT gf.ObjectID ,
'['+SCHEMA_NAME(schema_id)+'].['+Sch.name+']' AS FullName,
gf.TableName ,
gf.IndexID ,
IND.name AS IndexName ,
gf.PercentFragment ,
gf.TotalFrags ,
gf.PagesPerFrag ,
gf.NumPages
FROM GetFragmatation AS gf
INNER JOIN sys.indexes AS ind
ON ind.object_id = gf.ObjectID
AND index_id = gf.IndexID
INNER JOIN sys.tables AS Sch
ON Sch.object_id = gf.ObjectID
--Use the below one to defragment the Index
ALTER INDEX PK_TableNameIndexName
ON [dbo].[TableName]REBUILD WITH ( FILLFACTOR = 70, ONLINE = ON )
--To alter all the index for a table
--ALTER INDEX ALL ON TableName REBUILD
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent AS [Percentage of the logical index that is fragmented],
IPS.avg_fragment_size_in_pages AS [Average number of pages in a leaf-level fragment.],
IPS.avg_page_space_used_in_percent,
IPS.record_count AS [Total number of records],
IPS.ghost_record_count,
IPS.fragment_count AS [Number of fragments in the leaf level.],
IPS.avg_fragment_size_in_pages AS PagesPerFrag,
IPS.page_count AS [Number of index or data pages.]
FROM sys.dm_db_index_physical_stats(db_id(N'Rkpms'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND ST.TYPE = 'U'
ORDER BY 4 DESC
List Everything in My Database
SELECT B.Name as TableName,A.name as TriggerName
FROM sysobjects A,sysobjects B
WHERE A.xtype='TR'
AND A.parent_obj = B.id
Here are the list of all possible values for this column (xtype):
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
To list the triggers
SELECT S2.[name] TableName, S1.[name] TriggerName,
CASE
WHEN S2.deltrig = s1.id THEN 'Delete'
WHEN S2.instrig = s1.id THEN 'Insert'
WHEN S2.updtrig = s1.id THEN 'Update'
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'
Subscribe to:
Posts (Atom)