SQL SERVER – 2005List Tables in Database Without Primary Key, with Primary key, with Foreign Key

--SQL SERVER – 2005 – List Tables in Database Without Primary Key
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO

--SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

--SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Queries to get filegroup of table/indexes

--2005
select case when indexes.type_desc in ('HEAP','CLUSTERED')
then 'Table-' + indexes.type_desc
else ' NC Index' end as indexType,
rtrim(cast(
case when indexProperty(objects.object_id,indexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(schemas.name + '.'
+ indexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(objects.object_id,indexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
schemas.name + '.' + objects.name as tableName,
coalesce(indexes.name,'') as indexName,
filegroups.name as filegroup
from sys.indexes as indexes
join sys.objects
on indexes.object_id = objects.object_id
join sys.schemas
on objects.schema_id = schemas.schema_id
join sys.filegroups as filegroups
on indexes.data_space_id = filegroups.data_space_id
where objectproperty(indexes.object_id,'IsMSShipped') = 0
order by tableName, case when indexes.type_desc in ('HEAP','CLUSTERED') then 0 else 1 end

--2000 (Assumes that owner is DBO)
select case when indid = 0 then 'Table-Heap'
when indid = 1 then 'Table-Clustered'
else ' NC Index'
end as objectType,
rtrim(cast(
case when indexProperty(sysindexes.id,sysindexes.name,'IsUnique') = 1
then 'unique ' else '' end +
case when isNull(objectProperty(object_id(sysindexes.name),'IsConstraint'),0) = 1
then 'constraint ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsAutoStatistics') = 1
then 'auto ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsStatistics') = 1
then 'statistics ' else '' end +
case when indexProperty(sysindexes.id,sysindexes.name,'IsHypothetical') = 1
then 'hypothetical ' else '' end
as varchar(30))) as indexProperties,
object_name(sysindexes.id) as tableName,
coalesce(sysindexes.name,'') as indexName,
sysfilegroups.groupname as filegroup
from sysindexes
join sysfilegroups
on sysindexes.groupId = sysfilegroups.groupId
where objectproperty(sysindexes.id,'IsMSShipped') = 0
order by object_name(sysindexes.id),indid

'Show Size, Space Used, Unused Space, Type, and Name of all database files'

--Posted - 02/14/2006 : 11:25:53
--------------------------------------------------------------------------------
--Edit 2007-8-9:
--Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.


-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005
set nocount on

print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name



create table #TABLE_SPACE_WORK
(
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE_USED
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MB numeric(18,4) not null,
USED_GB numeric(18,4) not null,
AVERAGE_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)
declare @fetch_status int
declare @proc varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME =
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'

-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end --While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED =
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA =
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE =
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED =
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go