Database

Sk8tzz wrote:

I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL.

DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor

CREATE TABLE #TempIndexes (table_name varchar(255),index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))
CREATE TABLE #TempHoldIndexes (index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))

DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN   
	SELECT @tablename = RTRIM(@tablename)
	INSERT #TempHoldIndexes EXEC sp_helpindex @tablename
	
	INSERT #TempIndexes SELECT @tablename, index_name,index_description,index_keys FROM #TempHoldIndexes
	DELETE FROM #TempHoldIndexes
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

SELECT * FROM #TempIndexes
DROP TABLE #TempIndexes
DROP TABLE #TempHoldIndexes

-- create temp table
create table #spindtab
(	
	table_name			sysname,
	index_name			sysname	collate database_default NOT NULL,
	stats				int,
	groupname			sysname collate database_default NOT NULL,
	index_keys			nvarchar(2126)	collate database_default NOT NULL -- see @keys above for length descr
)

DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor

declare @objname nvarchar(776)		-- the table to check for indexes
FETCH NEXT FROM tnames_cursor INTO @objname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN   
	SELECT @objname = RTRIM(@objname)
	-- PRELIM
	set nocount on

	declare @objid int,			-- the object id of the table
			@indid smallint,	-- the index id of an index
			@groupid smallint,  -- the filegroup id of an index
			@indname sysname,
			@groupname sysname,
			@status int,
			@keys nvarchar(2126),	--Length (16*max_identifierLength)+(15*2)+(16*3)
			@dbname	sysname

	-- Check to see that the object names are local to the current database.
	select @dbname = parsename(@objname,3)

	if @dbname is not null and @dbname <> db_name()
	begin
			raiserror(15250,-1,-1)
			--return (1)
	end

	-- Check to see the the table exists and initialize @objid.
	select @objid = object_id(@objname)
	if @objid is NULL
	begin
		select @dbname=db_name()
		raiserror(15009,-1,-1,@objname,@dbname)
		--return (1)
	end

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	declare ms_crs_ind cursor local static for
		select indid, groupid, name, status from sysindexes
			where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
	open ms_crs_ind
	fetch ms_crs_ind into @indid, @groupid, @indname, @status

	-- IF NO INDEX, QUIT
	if @@fetch_status < 0
	begin
		deallocate ms_crs_ind
		--raiserror(15472,-1,-1) --'Object does not have any indexes.'
		insert into #spindtab values (@objname, '', '', '','')
		CONTINUE
		--return (0)
	end



	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.
		declare @i int, @thiskey nvarchar(131) -- 128+3

		select @keys = index_col(@objname, @indid, 1), @i = 2
		if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
			select @keys = @keys  + '(-)'

		select @thiskey = index_col(@objname, @indid, @i)
		if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
			select @thiskey = @thiskey + '(-)'

		while (@thiskey is not null )
		begin
			select @keys = @keys + ', ' + @thiskey, @i = @i + 1
			select @thiskey = index_col(@objname, @indid, @i)
			if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
				select @thiskey = @thiskey + '(-)'
		end

		select @groupname = groupname from sysfilegroups where groupid = @groupid

		-- INSERT ROW FOR INDEX
		insert into #spindtab values (@objname,@indname, @status, @groupname, @keys)

		-- Next index
		fetch ms_crs_ind into @indid, @groupid, @indname, @status
	end
	deallocate ms_crs_ind

	-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
	declare @empty varchar(1) select @empty = ''
	declare @des1			varchar(35),	-- 35 matches spt_values
			@des2			varchar(35),
			@des4			varchar(35),
			@des32			varchar(35),
			@des64			varchar(35),
			@des2048		varchar(35),
			@des4096		varchar(35),
			@des8388608		varchar(35),
			@des16777216	varchar(35)
	select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
	select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
	select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
	select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
	select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
	select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
	select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
	select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
	select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
	end
	FETCH NEXT FROM tnames_cursor INTO @objname
end

CLOSE tnames_cursor
DEALLOCATE tnames_cursor

-- DISPLAY THE RESULTS
select
	'table_name' = table_name,
	'index_name' = index_name,
	'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
			case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
			+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
			+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
			+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
			+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
			+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
			+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
			+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
			+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
			+ ' located on ' + groupname),
	'index_keys' = index_keys
from #spindtab
order by index_name


--return (0) -- sp_helpindex

DROP TABLE #spindtab

SELECT supplier.sname, failures.count, supplier.count
FROM
(
    select suppliers.sname, count(*) AS count 
    from failures,devices,suppliers 
    where devices.suppid=suppliers.suppid 
    and failures.deviceid=devices.deviceid 
    group by suppliers.sname
) AS supplier
INNER JOIN
(
    select suppliers.sname, count(*) AS count 
    from devices,suppliers 
    where devices.suppid=suppliers.suppid 
    group by suppliers.sname
) AS failure ON supplier.sname = failure.sname

Colin Angus Mackay wrote:

Does this help?

SELECT failure.locid,device.count [devices],failure.count [failures]<br />
FROM<br />
{<br />
select locality.locid,count(*) from allotment,locality where allotment.locid=locality.locid group by locality.locid<br />
} AS device<br />
FULL OUTER JOIN<br />
{<br />
select locid,count(deviceid) from failures group by locid<br />
} AS failure ON device.locid = failure.locid

karinb wrote:

he designer keeps defaulting to a length of '16'