Sql query to find table, column names where columns are having unique index

Select distinct Tbl.name, col.name From
sys.tables Tbl Join sys.columns col on col.[object_id] = Tbl .[object_id]Join sys.index_columns idx_cols on idx_cols.[column_id] = col.[column_id] and idx_cols.[object_id] = col.[object_id]
Join sys.indexes idx on idx_cols.[index_id] = idx.[index_id] and idx.[object_id] = col.[object_id]where col.name = 'INT_EMP_ID'and idx.is_unique = 1


This query finds table names,column names where columns are having primary key or unique key or its unique index is set to 1.
By default this query works on all tables in database thus time efficient.
User can alter this query for selectiong scope to a single table or group of tables or complete database depending upon his necessity.

Read more »

Query in sql to find the names of table having some particular column with some particular data and also find the number of appearances of data

DECLARE @table_name VARCHAR(1000) -- table name Declare @COUNT VARCHAR(8000) -- count of the occurence of emp_id
DECLARE db_cursor CURSOR FOR select table_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAMe like '%INT_EMP_ID%'
OPEN
db_cursor FETCH NEXT FROM db_cursor INTO @table_nameWHILE @@FETCH_STATUS = 0 BEGIN
SET @COUNT = 'select count(*) AS ' +@table_name+ ' from ' + @table_name + ' where' + ' INT_EMP_ID = 222201787 'EXEC(@COUNT)
FETCH NEXT FROM db_cursor INTO @table_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Read more »