June 4, 2008 at 6:48 pm
asking for help in solving the following.
trying to exec
SET @cmd = ' use ? SELECT OBJECT_NAME(tbl.[id]) AS ''tablename'', idx.[NAME],
-------------------
-----------------------
FROM dbo.sysindexes idx
INNER JOIN dbo.sysobjects tbl
ON idx.[id]=tbl.[id]
INNER JOIN
( SELECT tbl.[id] AS ''tablename'', idx.[NAME],
-------------------
-------------------
-----------------------
FROM dbo.sysindexes idx
INNER JOIN dbo.sysobjects tbl
ON idx.[id]=tbl.[id]
WHERE OBJECT_NAME(idx.ID) NOT like ''sys%'' AND idx.indid > ''0''
AND INDEXPROPERTY( tbl.[id], idx.[name], ''IsStatistics'') = ''0''
)I2
ON I2.[NAME] <> idx.[name]
and
idx.[id]= tbl.[id]
and
ISNULL(INDEX_COL(tbl.[name], idx.indid, 1),'') = isnull(I2.col1,'')
AND
isnull(INDEX_COL( tbl.[name], idx.indid, 2 ),'') = isnull(I2.col2,'')
AND
isnull(INDEX_COL( tbl.[name], idx.indid, 3 ),'') = isnull(I2.col3,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 4 ),'') = isnull(I2.col4,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 5 ),'') = isnull(I2.col5,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 6 ),'') = isnull(I2.col6,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 7 ),'') = isnull(I2.col7,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 8 ),'') = isnull(I2.col8,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 9 ),'') = isnull(I2.col9,'')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 10 ),'') = isnull(I2.col10,'')'
exec sp_MSforeachDB @cmd
but i'm getting the following error
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'AND'.
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'AND'.
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'AND'.
Server: Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'AND'.
June 4, 2008 at 7:23 pm
Use the ...
print @cmd
instead of the exec to see what the query looks like. It looks like you have the single quotes off in the isnull section of the query. Also, the column names are invalid unless you left them out of the post where all the dashes are.
June 4, 2008 at 7:33 pm
already know what the query looks like, i used the ----- to shorten the thread. it's obviously a problem where the ISNULL section is probly i'm missing a qoute or somethin, can anyone else provide some help?
thx
June 4, 2008 at 7:54 pm
Replace the end of the query with this...
and
ISNULL(INDEX_COL(tbl.[name], idx.indid, 1),'''') = isnull(I2.col1,'''')
AND
isnull(INDEX_COL( tbl.[name], idx.indid, 2 ),'''') = isnull(I2.col2,'''')
AND
isnull(INDEX_COL( tbl.[name], idx.indid, 3 ),'''') = isnull(I2.col3,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 4 ),'''') = isnull(I2.col4,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 5 ),'''') = isnull(I2.col5,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 6 ),'''') = isnull(I2.col6,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 7 ),'''') = isnull(I2.col7,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 8 ),'''') = isnull(I2.col8,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 9 ),'''') = isnull(I2.col9,'''')
and
isnull(INDEX_COL( tbl.[name], idx.indid, 10 ),'''') = isnull(I2.col10,'''')'
June 4, 2008 at 9:15 pm
thx appreciate it
July 29, 2008 at 5:58 pm
somewhat related topic. I'm trying to use INDEXPROPERTY, STATS_DATE and other functions in command string for sp_msforeachdb. When I run query against single user database the values are populated mostly 0/1s or dates, but when I try similar query for sp_msforeachdb, the values are mostly NULL. any idea why?
Thanks...
here are the 2 code samples:
--for 1 database:
Use database_name
go
SELECT
TableName = o.name,
IndexName = i.name,
LastUpdatedDate = STATS_DATE(i.id, i.indid),
TableID = i.id,
IndexID = i.indid,
rowcnt,
rowmodctr,
rows,
IsClustered= INDEXPROPERTY(o.id , i.name , 'IsClustered' ),
IsUnique= INDEXPROPERTY(o.id , i.name , 'IsUnique' ),
IsStatistics= INDEXPROPERTY(o.id , i.name , 'IsStatistics' ) ,
IsAutoStatistics= INDEXPROPERTY(o.id , i.name , 'IsAutoStatistics' ) ,
IsHypothetical= INDEXPROPERTY(o.id , i.name , 'IsHypothetical' ),
IsMSShipped= OBJECTPROPERTY(o.id , 'IsMSShipped' ),
IndexDepth= INDEXPROPERTY(o.id , i.name , 'IndexDepth' )
FROM dbo.sysobjects AS o WITH (nolock)
INNER JOIN dbo.sysindexes AS i WITH (nolock)
ON o.id = i.id
ORDER BY TableName , IndexID
--for all databases
exec sp_msforeachdb
@command1 =
'
SELECT
''?'' as DatabaseName,
TableName = o.name,
IndexName = i.name,
LastUpdatedDate = STATS_DATE(i.id, i.indid),
TableID = i.id,
IndexID = i.indid,
rowcnt,
rowmodctr,
rows,
IsClustered= INDEXPROPERTY(o.id , i.name , ''IsClustered'' ),
IsUnique= INDEXPROPERTY(o.id , i.name , ''IsUnique'' ),
IsStatistics= INDEXPROPERTY(o.id , i.name , ''IsStatistics'' ) ,
IsAutoStatistics= INDEXPROPERTY(o.id , i.name , ''IsAutoStatistics'' ) ,
IsHypothetical= INDEXPROPERTY(o.id , i.name , ''IsHypothetical'' ),
IsMSShipped= OBJECTPROPERTY(o.id , ''IsMSShipped'' ),
IndexDepth= INDEXPROPERTY(o.id , i.name , ''IndexDepth'' )
FROM [?].dbo.sysobjects AS o WITH (nolock)
INNER JOIN [?].dbo.sysindexes AS i WITH (nolock)
ON o.id = i.id
ORDER BY TableName , IndexID
'
July 29, 2008 at 7:09 pm
Because functions INDEXPROPERTY and OBJECTPROPERTY are executed locally.
_____________
Code for TallyGenerator
July 29, 2008 at 7:22 pm
thanks for your reply...I'm assuming that applies to STATS_DATE as well?
rg
July 29, 2008 at 7:41 pm
yes
_____________
Code for TallyGenerator
July 29, 2008 at 7:51 pm
This should be really useful for you:
USE master
GO
CREATE VIEW INFORMATION_SCHEMA.Indexes
AS
SELECT O.Name as ObjectName, i.name, i.status, first, {whatever else you want to know about indexes}
FROM sysobjects O
inner join sysindexes i on o.id = i.id
GO
USE MyDatabase
GO
select * from INFORMATION_SCHEMA.Indexes
Or just
select * from MyDatabase.INFORMATION_SCHEMA.Indexes
_____________
Code for TallyGenerator
July 29, 2008 at 7:57 pm
thanks again...
any other easy/proven way to query the last updated date for stats across all databases/tables/indexes? ideas are welcome, as I'm guessing I'll be writing a custom script to do so...
rg
July 30, 2008 at 5:40 pm
Regarding your initial question. The length of the @cmd variable may be too short to accept all the dynamic text you are trying to insert. In this case, it would truncate the SQL statement and an error would be reported.
I've done this too myself on several occasions.
July 31, 2008 at 9:06 pm
This should work for you:
USE master
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'SP_AllIndexes_Properties' AND type = 'P')
DROP PROCEDURE SP_AllIndexes_Properties
GO
CREATE PROCEDURE dbo.SP_AllIndexes_Properties -- must be "sp_"
AS
-- Just copy-paste of your query, no chages here
SELECT
TableName = o.name,
IndexName = i.name,
LastUpdatedDate = STATS_DATE(i.id, i.indid),
TableID = i.id,
IndexID = i.indid,
rowcnt,
rowmodctr,
rows,
IsClustered = INDEXPROPERTY(o.id , i.name , 'IsClustered' ),
IsUnique = INDEXPROPERTY(o.id , i.name , 'IsUnique' ),
IsStatistics = INDEXPROPERTY(o.id , i.name , 'IsStatistics' ) ,
IsAutoStatistics = INDEXPROPERTY(o.id , i.name , 'IsAutoStatistics' ) ,
IsHypothetical = INDEXPROPERTY(o.id , i.name , 'IsHypothetical' ),
IsMSShipped = OBJECTPROPERTY(o.id , 'IsMSShipped' ),
IndexDepth = INDEXPROPERTY(o.id , i.name , 'IndexDepth' )
FROM dbo.sysobjects AS o WITH (nolock)
INNER JOIN dbo.sysindexes AS i WITH (nolock) ON o.id = i.id
ORDER BY TableName , IndexID
go
USE pubs
GO
EXEC dbo.SP_AllIndexes_Properties
GO
-- another way to address different databases
EXEC Northwind.dbo.SP_AllIndexes_Properties
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply