SELECT c.name 'Column Name',
t.Name 'Data type', c.max_length 'Max Length', c.precision , c.scale , c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('npidata_20050523-20150412')
SELECT 'DB_NAME' = db.name, 'FILE_NAME' = mf.name, 'FILE_TYPE' = mf.type_desc, 'FILE_PATH' = mf.physical_name FROM sys.databases db INNER JOIN sys.master_files mf ON db.database_id = mf.database_id WHERE db.state = 6 -- OFFLINE 0 = ONLINE 1 = RESTORING 2 = RECOVERING 3 = RECOVERY_PENDING 4 = SUSPECT 5 = EMERGENCY 6 = OFFLINE