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));

Find all SQL Identity columns

SELECT '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
table_name ,
column_name ,
ordinal_position ,
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 ,
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

--To alter all the index for a table

SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
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.record_count AS [Total number of records],
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'

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