



CREATE PROCEDURE DBO.FindFields (@tablenames VARCHAR(8000) = '', @columnnames VARCHAR(8000) = '', @includecollation BIT = 0)
AS
SET NOCOUNT ON
/*
COMMENT:            
- KLJ 20040422 Created

DESCRIPTION:
- Made to find columns in entire database 

PRĘ: 
- @tablenames  = Those tables to look in
- @columnnames = Those columns to look for

POST:                      
- tables with columns

*/

  DECLARE @table_name VARCHAR(50)
  DECLARE @table VARCHAR(50)
  DECLARE @table_col VARCHAR(50)
  DECLARE @collation VARCHAR(50)

IF @tablenames <> '' 
BEGIN
  DECLARE #table_cur CURSOR FOR
    SELECT name FROM sysobjects WITH (NOLOCK) 
    WHERE xtype = 'U' 
      AND name in (@tablenames)
    ORDER BY name
  OPEN #table_cur
END
ELSE
BEGIN
  DECLARE #table_cur CURSOR FOR
    SELECT name FROM sysobjects WITH (NOLOCK) 
    WHERE xtype = 'U' 
      AND name NOT LIKE '???%'
      AND name NOT LIKE 'zzz%'
    ORDER BY name
  OPEN #table_cur
END

FETCH NEXT FROM #table_cur INTO @table_name
WHILE @@FETCH_status = 0
BEGIN
  IF @columnnames = '' 
  BEGIN
    DECLARE #col_cur CURSOR FOR
      SELECT sysobjects.name, syscolumns.name, syscolumns.collation FROM syscolumns WITH (NOLOCK) INNER JOIN sysobjects WITH (NOLOCK) ON sysobjects.id = syscolumns.id where sysobjects.name = @table_name order by syscolumns.name
    OPEN #col_cur
  END
  ELSE
  BEGIN
    DECLARE #col_cur CURSOR FOR
      SELECT sysobjects.name, syscolumns.name, syscolumns.collation FROM syscolumns WITH (NOLOCK) INNER JOIN sysobjects WITH (NOLOCK) ON sysobjects.id = syscolumns.id where sysobjects.name = @table_name and syscolumns.name in (@columnnames) order by syscolumns.name
    OPEN #col_cur
  END

  FETCH NEXT FROM #col_cur INTO @table, @table_col, @collation

  WHILE @@FETCH_status = 0
  BEGIN
    if @includecollation = 1 
      PRINT @table_name + ': - ' + convert(char(25), ltrim(rtrim(@table_col))) + ' - '  + convert(char(35), ltrim(rtrim(@collation)))
    else    
      PRINT @table_name + ': - ' + convert(char(25), ltrim(rtrim(@table_col)))
    FETCH NEXT FROM #col_cur INTO @table, @table_col, @collation
  END
  
  CLOSE #col_cur
  DEALLOCATE #col_cur
  FETCH NEXT FROM #table_cur INTO @table_name
END
DEALLOCATE #table_cur

