Search column too

[BenUHP]

G'day Mate,

Great extension use it every day since I installed ADS, any chance you'd make it so I can search for colum names too? Would help heaps with my workload

Thanks

[MikhailProfile]

Hey Ben,

Do you mean that when you type a column name, in the search bar you want to see all tables that contains this column?

[BenUHP]

100% yeah you got it. If it's got a negative impact on the speed making it a configurable item could fix that.

[MikhailProfile]

Hey Ben,

I have added a setting that allows you to include column names to Tables.  You could try it in the new release 1.1. 

[BenUHP]

When switching between database groups i get this error and only if I have the new feature enabled

The first group I select works no problems, change to another group get that error but if I switch back to the first group works fine.

[MikhailProfile]

Hey Ben,

It seems that there is some error during the query.

Can you run this at target database and back with results?

` -- Create a temporary table to store the results CREATE TABLE #TableColumns ( SchemaName NVARCHAR(128), TableName NVARCHAR(128), ColumnList NVARCHAR(MAX) )

-- Declare variables for dynamic SQL DECLARE @schemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @sql NVARCHAR(MAX)

-- Cursor to iterate through each table DECLARE tableCursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Exclude views

-- Loop through each table and generate dynamic SQL OPEN tableCursor FETCH NEXT FROM tableCursor INTO @schemaName, @tableName

WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO #TableColumns (SchemaName, TableName, ColumnList) ' + 'SELECT ''' + @schemaName + ''', ''' + @tableName + ''', ' + 'STUFF((SELECT '', '' + COLUMN_NAME ' + 'FROM INFORMATION_SCHEMA.COLUMNS ' + 'WHERE TABLE_SCHEMA = ''' + @schemaName + ''' ' + 'AND TABLE_NAME = ''' + @tableName + ''' ' + 'FOR XML PATH('''')), 1, 2, '''')'

EXEC sp_executesql @sql

FETCH NEXT FROM tableCursor INTO @schemaName, @tableName

END

-- Close and deallocate the cursor CLOSE tableCursor DEALLOCATE tableCursor

SELECT o.name AS name , 'TABLE' AS type , tableColumns.ColumnList AS DEFINITION , ss.name AS schemaName FROM sys.objects AS o JOIN sys.schemas AS ss ON o.schema_id = ss.schema_id JOIN #TableColumns as tableColumns on tableColumns.TableName = o.name AND tableColumns.SchemaName = ss.name

WHERE type = 'U'

UNION ALL

SELECT DISTINCT o.name AS name , o.type_desc AS type , m.DEFINITION AS DEFINITION , ss.name AS schemaName FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id JOIN sys.schemas AS ss ON o.schema_id = ss.schema_id

-- Drop the temporary table DROP TABLE #TableColumns `

[BenUHP]

Morning,

Yep as soon as I saw your code, my mind went straight to database collation. Something we have learnt to deal with on our end very quickly with our whitebox DBs. I had to add COLLATE DATABASE_DEFAULT to 3 places in your code.

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

`-- Create a temporary table to store the results CREATE TABLE #TableColumns ( SchemaName NVARCHAR(128) , TableName NVARCHAR(128) , ColumnList NVARCHAR(MAX) )

-- Declare variables for dynamic SQL DECLARE @schemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @Sql NVARCHAR(MAX)

-- Cursor to iterate through each table DECLARE tableCursor CURSOR FOR SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Exclude views -- Loop through each table and generate dynamic SQL

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @schemaName , @tableName

WHILE @@FETCH_STATUS = 0 BEGIN SET @Sql = 'INSERT INTO #TableColumns (SchemaName, TableName, ColumnList) ' + 'SELECT ''' + @schemaName + ''', ''' + @tableName + ''', ' + 'STUFF((SELECT '', '' + COLUMN_NAME ' + 'FROM INFORMATION_SCHEMA.COLUMNS ' + 'WHERE TABLE_SCHEMA = ''' + @schemaName + ''' ' + 'AND TABLE_NAME = ''' + @tableName + ''' ' + 'FOR XML PATH('''')), 1, 2, '''')'

EXEC sp_executesql @sql

FETCH NEXT
FROM tableCursor
INTO @schemaName
    , @tableName

END

-- Close and deallocate the cursor CLOSE tableCursor

DEALLOCATE tableCursor

SELECT o.name AS name , 'TABLE' AS type , tableColumns.ColumnList AS DEFINITION , ss.name AS schemaName FROM sys.objects AS o JOIN sys.schemas AS ss ON o.schema_id = ss.schema_id JOIN #TableColumns AS tableColumns ON tableColumns.TableName = o.name COLLATE DATABASE_DEFAULT AND tableColumns.SchemaName = ss.name COLLATE DATABASE_DEFAULT WHERE type = 'U'

UNION ALL

SELECT DISTINCT o.name AS name , o.type_desc AS type , m.DEFINITION COLLATE DATABASE_DEFAULT AS DEFINITION , ss.name AS schemaName FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id JOIN sys.schemas AS ss ON o.schema_id = ss.schema_id

-- Drop the temporary table DROP TABLE #TableColumns `

[MikhailProfile]

Hey Ben,

I have updated the request. Should work now, please, try it out.

[BenUHP]

That works great thanks mate.