Getting an index's information

I was asked by one of the client to get all info about the indexes i.e name of the index , column's name, table's name etc. Please take a look at following script to return the data for SQL Server 2000.
use pubs

SELECT tbl = object_name(i.id), i.name as index_name, c.name as column_name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
FROM sysindexes i
JOIN syscolumns c on i.id = c.id
JOIN sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
LEFT JOIN sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
AND objectproperty(i.id,'IsMSShipped')=0
ORDER BY tbl, i.name, k.keyno

For SQL Server 2005 I use the script written by Kalen Delaney.Remember there is new type of index called INCLUDE.

CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name,
'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id

After creating the view, you can select from it, and it will give you the
KEY columns and the INCLUDED columns in all the indexes in all the tables.
Or, you can add a WHERE clause for your own table or index:

SELECT * FROM get_index_columns
WHERE object_name = 'mytable'

Foreign Key dependency

Some time ago I was asked to truncate lots of tables in client's database.As such,all those tables have FK and PK dependency. You get the error like that if you run TRUNCATE TABLE Categories in Nortwind database as an example.
--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

So , there is no such great problem as you know the database structure and can easily indetify parent-child tables. But what if you don't?
I'd like to show the function (sadly, I don't remember by whom it is written) that returns the tables ordered by its level. Level 0 means that it is refernced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2

Object's modified date

There are lots of businesses have already upgraded to SQL Server 2005.At this time I'd like to take a look at great feature that we have in SQL Server 2005. I have been asked so many times how to know WHEN does the object change? It is a headache in SQL Server 2000. Fortunately, now we can answer that question.

Let's say the user changed number of stored procedures.Using the following statement we can easily get the data.

SELECT name,modify_date FROM sys.procedures
ORDER BY modify_date desc

But let's take it a little bit futher. I'd like to know when does user update the table last time?

CREATE TABLE dbo.uri(c INT NOT NULL PRIMARY KEY)
GO
INSERT INTOdbo.uri VALUES(10)
--we got one entry

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

---try to change the data
UPDATE dbo.uri SET c =50 WHERE c =10

--you'll see that last_user_update is changed

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

So ,you can play with these Dynamic Management Views to obtain more info.