|
Why not use sp_helpindex?
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
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Or, here is a modified version of sp_helpindex that you can run from the query analyzer:
-- 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
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Thanks, will test, trying to write a unique little DAL that does not require any generated code, strongly typed as well.
.NET Rules
|
|
|
|
|
I need to integrate the 2 results of the following query
select suppliers.sname, count(*) i from failures,devices,suppliers where devices.suppid=suppliers.suppid and failures.deviceid=devices.deviceid group by suppliers.sname
output:
sname i
***** **
select suppliers.sname, count(*) j from devices,suppliers where devices.suppid=suppliers.suppid group by suppliers.sname
output:
sname j
***** **
I need the output to be in the following form:
sname i j
***** ** **
Please help. Thanks
|
|
|
|
|
You can put them in subqueries and join the result set of the subqueries together. Like this:
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
The above query can be could be optimised further, but I wanted to show as much of your original SQL as possible so you can see how it fits together. It also assumes that sname is unique.
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Colin Angus Mackay wrote: Does this help?
Thank you , Mr. Colin, The SQL query provided by you is working fine. That solves my problem.
|
|
|
|
|
But wait !! inner join needs both queries to have common records existent for display.
Replace Inner Join with Full Outer Join so that even of any record doesnt have a matching record in other query, still it comes in display.
Best Regards,
Darpan Gogia
|
|
|
|
|
Thanks.
The failure count is returning null value instead of that i need to display it as numeric value 0.
|
|
|
|
|
you can use the coalesce function to get 0's.
|
|
|
|
|
Is the following is the format of coalesce?
coalesce(colname,0)
Please tell me how to display decimal value for the following query?
select 1/2
Thanks
|
|
|
|
|
yes this is correct form of coalesce.
in the second question, you are performing calculation on integer so it returns integer. you need to cast/convert the result to decimal/float to get the decimal value of the query as:
CAST(1/2 as double), convert check the Books online for syntax help!!
Best Regards,
Darpan Gogia
|
|
|
|
|
Thanks
The CAST(1/2 as double) is displaying an error
I've tried CAST(1 as float)/CAST(2 as float) it is working.
Thank you very much again.
|
|
|
|
|
Hi,
The following code is displaying "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" error
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
device is used to find number of device in a particular locality
failure is to find the number of failures in the particular location
All localities are stored in locality table. the alloted devices are in allotment table, failures are in failures table
Thanks
-- modified at 9:43 Wednesday 19th April, 2006
|
|
|
|
|
check the syntax in query as well as your servers.
Example, dont use reserved words as "count" in your query directly as "device.count [devices],"
Most probably you will end up getting some silly mistake with your query, or else with permissions.
|
|
|
|
|
There is no problem with the server and the permissions. Please help same error.
SELECT failure.locid,device.count,failure.count
FROM
{
select locality.locid,count(*) AS count
from allotment,locality
where allotment.locid=locality.locid
group by locality.locid
} AS device
FULL OUTER JOIN
{
select locid,count(deviceid) AS count
from failures
group by locid
} AS failure ON device.locid = failure.locid
|
|
|
|
|
There is a syntax error in the query. I have to use normal brackets () instead of braces {}
Thanks
|
|
|
|
|
I need to utilize the text datatype, as i'm storing a very large amount of text data in a table. however, the designer keeps defaulting to a length of '16', and will not go any bigger. Am I doing something wrong?
I am at this time attempting to hard code data to the table as I'm archiving a lot of information, then i'll build the .aspx pages to keep it updated with the current data. If this doesn't work, i'll have to create 5 or 6 varchar rows, and i would rather avoid that. this field will be searchable eventually.
Thanks for any help you can offer.
karinb
|
|
|
|
|
karinb wrote: he designer keeps defaulting to a length of '16'
Although that is the length set by the designer, it is not the length allowed for data storage. Try storing your data...you will see that it fits just fine.
SQL Server Booksonline
Unless the text in row option is specified, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored. For more information about the storage of text, ntext, or image strings, see text, ntext, and image Data.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
You don't have the "Text In Row" option turned on do you?
SQL Server Booksonline
You enable the text in row option for a table by using sp_tableoption . With the text in row option set to ON, Microsoft® SQL Server 2000 stores text, ntext, or image strings directly in the data row
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
please explain how to do that, i do not know where i would look to enable that feature. Thank you.
karinb
|
|
|
|
|
You do not want it on, you want it off.
Have you actually tried to store your data? Do you receive an error?
Try this from the query anazyzer:
sp_tableoption N'TableName', 'text in row', 'OFF'
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
to answer this, no, i don't get an error at all, just am not able to put any data into the field if it is set for 'text'. the words, Long Text, are in the field instead. Is this just some sort of link to the actual data? how do I see if it is in there? I am actually copying/pasting text from a document and it includes some html tags. would that cause a problem?
thank you.
karinb
|
|
|
|
|
karinb wrote: I am actually copying/pasting text from a document and it includes some html tags. would that cause a problem?
Yes. Some of the characters in the html are probably causing the problem. Try using the query analyzer, or some other tool, to get/set the data. The enterprise manager isn't a very good tool for data manipulation.
Gotta go for the day. Bye.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I disabled the 'text in row'. I reset my field to 'text', and the length changed to '16'. Now, in the field when i open the table, this <long text=""> is in the box, and i am unable to enter my data into the field.
i am confused, why wouldn't it allow me to declare text and then put all my data in the row? i thought text held over 2bill chars?
karinb
-- modified at 17:45 Monday 17th April, 2006
|
|
|
|
|
In a database management project ,we have used Oracle at back-end and VB.Net at front-end.
In Oracle ,we used object-oriented features and created nested tables.Now we want to accesss those nested tables using ADO.Net .We are in search of answers of following questions:
1.Does Dataset class of ADO.Net support nested tables ?
2.If yes,can XML be used to specify schema of nested dataset ?
3.If not,how to manipulate nested tables of Oracle in ADO.Net?
As a temporary solution , we are retrieving main table and child table in two different tables;but this mechanism of database handling is affecting preformance of project badly.So we are in need of some other efficient solution.We will be thankful for your response if you will be able to help us to get out of the problem .Please reply as early as possible.
kam_ps
|
|
|
|
|