February 22, 2014 at 7:31 pm
Comments posted to this topic are about the item Get the all the tables on the server which do not have Clustered index
Regards,
Mitesh OSwal
+918698619998
February 24, 2014 at 2:14 pm
Nice script.
Works relatively quick, and could be used for troubleshooting performance issues.
The script was initially failing on all databases with the spaces in their names (common for the SharePoint databases). Need to bracket database names in joins.
See below corrected:
---Verify if any table dnt have the cluster key
DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
DBName NVARCHAR(100),
TableName NVARCHAR(100)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT ''?'',ts.Name
FROM [?].sys.tables ts
LEFT JOIN [?].sys.indexes si
ON ts.object_id = si.object_id
AND si.type = 1
INNER JOIN [?].sys.databases d
ON d.Name=''?'' AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
WHERE si.index_id IS NULL
'
INSERT INTO @tbl
EXECUTE sp_MSforeachdb @SQL
select * from @tbl
Alex Donskoy
SQL DBA Mimai FL.
February 25, 2014 at 7:27 am
Thanks for posting this script. I took what you did and applied it to another SQL I got here that looked for values in stored procedures. I don't remember who posted that to give them credit, the other one looped through for each database on the server. But here is what I came up with:
/* This will search for the value in @SearchText in stored procedures
across all databases on the server being run. */
DECLARE @SQL VARCHAR(8000)
DECLARE @SearchText NVARCHAR(255)
SET @SearchText = 'text to search for here'
DECLARE @Results TABLE
(
[SERVERNAME] VARCHAR(255),
[DBName] VARCHAR(255),
[NAME] VARCHAR(255),
XTYPE VARCHAR(255)
)
;
SELECT @SQL =
'SELECT DISTINCT @@SERVERNAME, ''?'' AS [DBName], so.[name], so.xtype
FROM ?.dbo.sysobjects so WITH(NOLOCK)
inner join ?.dbo.syscomments sc on
so.id = sc.id
INNER JOIN ?.sys.databases d ON
d.Name=''?''
AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
WHERE sc.text like ''%'+ @SearchText + '%'''
INSERT INTO @Results
EXEC sp_MSforeachdb @SQL
SELECT *
FROM @Results
ORDER BY DBName, XType, [Name]
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply