Searching Column Name in All Tables

http://www.sqlservercentral.com/scripts/68998/

*********************************************
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%ColumnName%'


*********************************************

Find Text in all columns of all tables in a Database

http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

**********************************************************************
DECLARE @TEXT VARCHAR(500)
SET @TEXT = 'DBAdmin'

DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500), ColumnName VARCHAR(500))
INSERT INTO @TABLES(TableName, ColumnName)
SELECT O.[NAME], C.[NAME]--SELECT *
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
ON C.ID = O.ID
WHERE O.XTYPE = 'U'
AND C.XTYPE NOT IN
(
127 --bigint
, 173 --binary
, 104 --bit
, 61 --datetime
, 106 --decimal
, 62 --float
, 34 --image
, 56 --int
, 60 --money
, 108 --numeric
, 59 --real
, 58 --smalldatetime
, 52 --smallint
, 122 --smallmoney
, 189 --timestamp
, 48 --tinyint
, 36 --uniqueidentifier
, 165 --varbinary
)
ORDER BY O.[NAME], C.[NAME]

IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#TMPREPORT%')
BEGIN
DROP TABLE #TMPREPORT
END
CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500), COLUMNNAME VARCHAR(500))

DECLARE @CNTR INT, @POS INT, @TableName VARCHAR(500), @ColumnName VARCHAR(500), @SQL VARCHAR(8000)
SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''
FROM @TABLES

--SELECT @POS, @CNTR, * FROM @TABLES

WHILE @POS <= @CNTR
BEGIN
SELECT @TableName = TableName, @ColumnName = ColumnName
FROM @TABLES
WHERE [ID] = @POS

SELECT @SQL = 'SELECT COUNT(*), ''' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS VARCHAR) LIKE ''%' + @TEXT + '%'''
--PRINT @SQL
BEGIN TRY
INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME)
EXEC(@SQL)
END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT @SQL
END CATCH
SELECT @POS = @POS + 1
END

SELECT * FROM #TMPREPORT WHERE COUNTER > 0
DROP TABLE #TMPREPORT
----------------------------------------------------------------------------------------
/*127 : bigint
173' --binary
104' --bit
175' --char
61' --datetime
106' --decimal
62' --float
34' --image
56' --int
60' --money
239' --nchar
99' --ntext
108' --numeric
231' --nvarchar
59' --real
58' --smalldatetime
52' --smallint
122' --smallmoney
98' --sql_variant
231' --sysname
35' --text
189' --timestamp
48' --tinyint
36' --uniqueidentifier
165' --varbinary
167' --varchar
*/
**********************************************************************

With Sql Generate Parameters for store procedure

******************************************************************************
select '@' + LOWER(substring(column_name,1,1)) + substring(column_name,2,Len(column_name)) + ' ' + UPPER(Data_Type) + CASE WHEN character_maximum_length is null THEN ',' ELSE ' (' + CAST(character_maximum_length as VARCHAR(200))+ '),' END from Information_Schema.columns
where Table_Name ='studentbatchResult'
*******************************************************************************
select 'db.AddInParameter(command, "@' +
LOWER(substring(column_name,1,1)) + substring(column_name,2,Len(column_name)) + '", DbType.' +
CASE WHEN Data_Type = 'VARCHAR' THEN 'String' WHEN Data_Type = 'int' THEN 'Int32'
WHEN Data_Type = 'DateTime' Then 'DateTime' WHEN Data_Type = 'bit' THEN 'Boolean' WHEN Data_Type = 'text' THEN 'String' END + ', objUser.' + column_Name + ');' from Information_Schema.columns where Table_Name ='tbl_UserDetails'
********************************************************************************

with Sql Generate DAL Parameters

***********************************************************************
select 'if (string.IsNullOrEmpty(myBatchFileResult.' + substring(column_name,1,Len(column_name))+')){
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name)) +'", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name))+'", myBatchFileResult.'+ substring(column_name,1,Len(column_name)) +');



}'

from Information_Schema.columns where Table_Name ='BatchUploadResult'
***************************************************


select 'if (string.IsNullOrEmpty(myBatchFileResult.' + substring(column_name,1,Len(column_name))+')){
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name)) +'", DBNull.Value);
}
else
{
myCommand.Parameters.AddWithValue("@' + substring(column_name,1,Len(column_name))+'", myBatchFileResult.'+ substring(column_name,1,Len(column_name)) +');



}'

from Information_Schema.columns where Table_Name ='StudentBatchResult'
*************************************************************************

With Sql Generate C# class code for table

http://www.sqlservercentral.com/scripts/Development/61477/

NOTE:first signup, if you are not registered with this website


*************************************************
CREATE PROCEDURE usp_TableToClass
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' + CHAR(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) + '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'

SELECT code FROM @temp
ORDER BY sort

*************************************************

Error Handling: Catching Errors With ASP.Net and C#

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=68

Login failed for user 'SQL2K802\Guest'

http://forums.asp.net/t/1409902.aspx