CODEWITHSUNDEEP
sqlsql-server
RalphBiggerton
RalphBiggerton

Reputation: 199

Finding specific data in a database

I have the following query from which to search for a given column name in a database. However, I'd like to be able to show the first line of the column as well so I can see what data is contained within the columns.

Is there an effective way to do this? 

SELECT      c.name  AS 'ColumnName'
           ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%Insert Data Here%'
ORDER BY    TableName
           ,ColumnName;

Upvotes: 0

Views: 143

Answers (2)

GreyOrGray
GreyOrGray

Reputation: 1729

You can build a select into a variable and execute it like below. You may run into issues depending on your data types (for example, I have a column that results in this message depending on my search value

Explicit conversion from data type image to varchar(max) is not allowed.

For normal data types, however, this should work for you:

DECLARE @searchKey NVARCHAR(100) = 'name' --this is what you're looking for in the column name
    , @selectCommand NVARCHAR(MAX)

SELECT @selectCommand = COALESCE(@selectCommand + ' union all ','') + 'select top 1 '''+ i.COLUMN_NAME+''' AS ColumnName, ''['+ i.TABLE_SCHEMA+'].['+ i.TABLE_NAME+']'' AS TableName, try_convert(varchar(max),'+ i.COLUMN_NAME+') AS SampleData FROM ['+ i.TABLE_SCHEMA+'].['+ i.TABLE_NAME+'] '
FROM INFORMATION_SCHEMA.COLUMNS i 
WHERE i.COLUMN_NAME LIKE N'%'+@searchKey+'%'
EXEC(@selectCommand)

Results look like this with a @searchKey of 'name':

ColumnName   TableName              SampleData
name         [dbo].[table_1]        person 1
NAME         [dbo].[asffaef]        rose
name         [dbo].[tablefeoe]      ashely
DatabaseName [dbo].[Untitled - 3]   NULL
LoginName    [dbo].[Untitled - 3]   NULL

Upvotes: 2

sgeddes
sgeddes

Reputation: 62851

I originally thought you could do this in a single dymaic sql statement, but then realized that wouldn't work. So unfortunately this is one of those cases where you have to use a cursor with dynamic sql:

CREATE TABLE #Results (tableName varchar(100), columnName varchar(100), val varchar(max))

DECLARE @SQLString nvarchar(500) 
DECLARE @TableName nvarchar(100)
DECLARE @ColumnName nvarchar(100)

DECLARE tables_columns_cursor CURSOR FOR
SELECT      c.name  AS ColumnName
           ,t.name AS TableName
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%Insert Data Here%'
ORDER BY    TableName
           ,ColumnName;

OPEN tables_columns_cursor
FETCH NEXT FROM tables_columns_cursor INTO @ColumnName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLString = 'INSERT INTO #Results 
                      SELECT TOP 1 ''' + @ColumnName + ''' AS ColumnName
                           , ''' + @TableName + ''' AS TableName
                           , ' + @ColumnName + ' 
                      FROM ' + @TableName

    EXECUTE sp_executesql @SQLString

    FETCH NEXT FROM tables_columns_cursor INTO @ColumnName, @TableName
END
CLOSE tables_columns_cursor
DEALLOCATE tables_columns_cursor

SELECT * FROM #Results
DROP TABLE #Results

Upvotes: 0

Related Questions