Gwud2uzU

1declare @IndexName NVARCHAR(128)='si_xdes_id'
2    declare @dbname NVARCHAR(128) = db_name()
3    DECLARE @sql NVARCHAR(MAX)
4    DECLARE @ParamDefinition NVARCHAR(MAX)
5    DECLARE @retval INT  
6
7IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
8   BEGIN
9
10        SELECT @sql = 'SELECT @retvalOUT = CASE WHEN EXISTS (
11                               SELECT * FROM ' + @dbname + '.sys.indexes I WHERE I.Name = @IndexName  ) 
12                               THEN 1 ELSE 0 END '
13
14        SET @ParamDefinition = N'@retvalOUT int OUTPUT,
15                                @IndexName SYSNAME';
16
17        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;
18
19        IF (@retval = 0) 
20            BEGIN
21
22               --RAISERROR('The index called %s does not exist on database %s',16,1,@IndexName,@dbname)
23               RETURN
24
25            END 
26   END 
27     
28DECLARE @sql NVARCHAR(MAX)=
29
30'   declare @IndexName NVARCHAR(128)=''uc_emailAddress''
31    declare @dbname NVARCHAR(128) = db_name()
32    DECLARE @sql NVARCHAR(MAX)
33    DECLARE @ParamDefinition NVARCHAR(MAX)
34    DECLARE @retval INT  
35
36IF @IndexName IS NOT NULL /*validate the index exists in the @dbname database*/
37   BEGIN
38
39        SELECT @sql = ''SELECT @retvalOUT = CASE WHEN EXISTS (
40                               SELECT * FROM '' + @dbname + ''.sys.indexes I WHERE I.Name = @IndexName  ) 
41                               THEN 1 ELSE 0 END ''
42
43        SET @ParamDefinition = N''@retvalOUT int OUTPUT,
44                                @IndexName SYSNAME'';
45
46        EXEC sp_executesql @SQL, @ParamDefinition, @retvalOUT= @retval OUTPUT, @IndexName  = @IndexName;
47
48        select @dbname,@retval
49
50        IF (@retval = 0) 
51            BEGIN
52
53               --RAISERROR(''The index called %s does not exist on database %s'',16,1,@IndexName,@dbname)
54               RETURN
55
56            END 
57   END
58'
59
60if object_id('tempdb..#radhe') is not null
61   drop table #radhe
62create table #radhe(dbname sysname, the_index_exist_here int)
63insert into #radhe
64EXEC sp_ineachdb @command = @sql, @user_only = 1;
65
66select * from #radhe