Tag Archive for find

Postgresql find duplicate columns

select event_id,user_id, count(*) from event_attend
group by event_id,user_id
HAVING count(*) > 1

source

Find Tables in Database (SQL Query)

Select * From sysobjects Where name like '%user%'

source

TSQL – Find All Empty Columns in a Database

-- Returns a list of all columns in current database
-- where the column's value is null for all records.
declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    ColumnName sysname,
    NotNullCnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare columnCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    where IS_NULLABLE = 'YES';

open columnCursor;

fetch next from columnCursor into @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0
begin
    -- use dynamic sql to get count of records where column is not null
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
        '] where [' + @columnName + '] is not null';
    -- print @sql; --uncomment for debugging
    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @columnName, @cnt;

    fetch next from columnCursor into @tableSchema, @tableName, @columnName;
end

close columnCursor;
deallocate columnCursor;

select * from @tempTable where NotNullCnt = 0;

source

Finding duplicates with SQL

# Suppose you want to find all email addresses in a table that exist more than once:

SELECT email, 
 COUNT(email) AS count
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )


# You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

source

recursively find a string in any js,php,inc,htm,html,css files

find -type f -regex ".*.(js|php|inc|htm[l]?|css)$" -exec grep -il 'searchstring' '{}' +

source

Howto list all the file extension types in an SVN log dump

svn log -v > myrepo.log

perl -ne 'print qq{$1
} if m{^s+.*(.[^.s]w+)$}' myrepo.log | sort | uniq -c | sort -r

source

Find a specific string in files of a certain type

find / -name "*.js" -exec grep -Hn H3qqea3ur6p {} ;

source

Find newest file and its modification time

find directory -type f | perl -e 'my $l=0; my $n=""; while( <> ) { chomp; my $t= ${[stat($_)]}[9]; $t > $l and ($l= $t, $n= $_); } print "$n	".localtime($l), "
";'

source

Change each ‘foo’ to ‘bar’ for all lines between line 5 and line 12.

:5,12s/foo/bar/g

source

Find mypage.htm current dir + subdirs

find -name 'mypage.htm'

source