July 10, 2012 at 8:58 pm
Hi
Actually i have a new client ,& their Database has no standard naming conventions & the application is in classic asp.I have a form in which a form there are many values in the different textboxes ,it it very difficult to trace the value come from which table.& also there is no erd.
I need a query from which i can get the table name with column name by giving Value
Thanks
July 10, 2012 at 10:22 pm
ALI100 (7/10/2012)
HiActually i have a new client ,& their Database has no standard naming conventions & the application is in classic asp.I have a form in which a form there are many values in the different textboxes ,it it very difficult to trace the value come from which table.& also there is no erd.
I need a query from which i can get the table name with column name by giving Value
Thanks
I did not understand your requirement what exactly you are looking for.
Either you need the tables used in that form or all the tables in database.
For first you can have SQL profiler while executing the form and you will get the associated tables and column.For second you can execute query on sys.tables and sys.columns
However to find the value in a perticular textbox you will have to debug your code.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 10, 2012 at 10:28 pm
let's suppose i have a value having lable name abc#= '6599912268'
& the new project has no ERD no standard of naming conventions... I need a fast way to know the abc# ='6599912268' is taking from which table & which column name.... like this the UI has many values which is time taken to trace manually
IS there any way to trace it
July 10, 2012 at 10:58 pm
ALI100 (7/10/2012)
let's suppose i have a value having lable name abc#= '6599912268'& the new project has no ERD no standard of naming conventions... I need a fast way to know the abc# ='6599912268' is taking from which table & which column name.... like this the UI has many values which is time taken to trace manually
IS there any way to trace it
As I told earlier, I don't think it is not possible to find the table name or column name associated with any form control except you will have to debug your code.
However you can post your query on some other forums like StakeOverflow in Classic ASP tab.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 11, 2012 at 6:01 am
A quick search on this site gave me the below link
I hope this will be helpfull
http://www.sqlservercentral.com/scripts/Miscellaneous/65769/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 6:10 am
i took over a screwed up database like that once; the table names and column names had been obfuscated, i guess in order to protect someones proprietary schema; they took out all the foreign keys too;
performance sucked, and it took me quite a while tracing the data to get a proper understanding of the schema;
I built a suite of views that mapped the tables/columns into human understandable relationships.
Lowell
July 11, 2012 at 6:12 am
Kingston Dhasian (7/11/2012)
A quick search on this site gave me the below linkI hope this will be helpfull
http://www.sqlservercentral.com/scripts/Miscellaneous/65769/
Hello Kingston,
If I am not wrong then he wants all columns of tables those are used in the form controls and that too specific of perticular control.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 11, 2012 at 6:19 am
here's what i use , a procedure i built called sp_UglySearch.
since you are searching every table/column for text, this is slow, and any tables with a Million rows are going to slow things down even more.
don't run this on production, but on a dev copy on a different machine.
usage is simple:
--a table and any columns that contain the searched value
EXEC sp_UGLYSEARCH '6599912268'
CREATE PROCEDURE sp_UGLYSEARCH
/*
--Purpose: to search every string column in a databasefor a specific word
--returns sql statement as a string which idnetifies the matching table
-- or when the optional parameter is used, the sql statement for the specific matching column.
--usage:
-- EXEC sp_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH '6599912268'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%' OR [TITLE] LIKE '%6599912268%'
--optional parameter SEARCHBYCOLUMN
-- EXEC sp_UGLYSEARCH '6599912268',1
-- creates one SQL for each Column that actually has a match for the searched value i.e.
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%'
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%6599912268%'
*/
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
objz.name AS TBLNAME,
colz.name AS COLNAME,
TYPE_NAME(colz.user_type_id) AS DATATYPE
INTO #TEMP
FROM sys.objects objz
INNER JOIN sys.columns colz ON objz.object_id = colz.object_id
WHERE objz.type='U'
AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END
ELSE --@SEARCHBYCOLUMN <> 0
BEGIN
DECLARE C2 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C2
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C2
DEALLOCATE C2
END --@SEARCHBYCOLUMN <> 0
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
Lowell
July 11, 2012 at 6:39 am
rhythmk (7/11/2012)
Kingston Dhasian (7/11/2012)
A quick search on this site gave me the below linkI hope this will be helpfull
http://www.sqlservercentral.com/scripts/Miscellaneous/65769/
Hello Kingston,
If I am not wrong then he wants all columns of tables those are used in the form controls and that too specific of perticular control.
He probably wants the names of database columns and their respective tables having a specific value
But, I am not sure and only the OP can confirm if my understanding is correct and that is what he wants
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 11, 2012 at 11:28 am
Lowell (7/11/2012)
here's what i use , a procedure i built called sp_UglySearch.since you are searching every table/column for text, this is slow, and any tables with a Million rows are going to slow things down even more.
don't run this on production, but on a dev copy on a different machine.
usage is simple:
--a table and any columns that contain the searched value
EXEC sp_UGLYSEARCH '6599912268'
CREATE PROCEDURE sp_UGLYSEARCH
/*
--Purpose: to search every string column in a databasefor a specific word
--returns sql statement as a string which idnetifies the matching table
-- or when the optional parameter is used, the sql statement for the specific matching column.
--usage:
-- EXEC sp_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH '6599912268'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%' OR [TITLE] LIKE '%6599912268%'
--optional parameter SEARCHBYCOLUMN
-- EXEC sp_UGLYSEARCH '6599912268',1
-- creates one SQL for each Column that actually has a match for the searched value i.e.
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%6599912268%'
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%6599912268%'
*/
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
objz.name AS TBLNAME,
colz.name AS COLNAME,
TYPE_NAME(colz.user_type_id) AS DATATYPE
INTO #TEMP
FROM sys.objects objz
INNER JOIN sys.columns colz ON objz.object_id = colz.object_id
WHERE objz.type='U'
AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END
ELSE --@SEARCHBYCOLUMN <> 0
BEGIN
DECLARE C2 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C2
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C2
DEALLOCATE C2
END --@SEARCHBYCOLUMN <> 0
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
Lowell, why do you use sys.columns instead of Information_Schema.Columns?
July 11, 2012 at 11:34 am
Just a note on forms. So far as I know, a label box doesn't actually point to a database & table. It is just a label. So trying to connect the two is difficult at best. On the other hand, if you are looking at a dropdown box or some other text box (different from a label) you may have the ability to locate the database & table names by looking at the properties. Or you could search the form's XML code and compare that against your list of table and database names.
If all your boxes are unbound (which can happen), I wish you the best of luck. Unbound boxes are a PITA. Chances are the details are in client code that you can't see without viewing the code for the form. Then maybe you can search that and find any stored procedures or functions, then reference back to your tables.
July 11, 2012 at 11:43 am
the opposite question for me really, is what advantage would there be to use the information_schema instead of the sys tables?
typically there's more information available in the SQL specific meta data, although in this case my query does not take advantage of it, and you are right, the other views could be used instead.
at one point, i was trying to create some TSQL code that could make a CREATE TABLE statement from the metadata;
information_schema does not contain a lot of the information you need to do that.
try tracking down things like which column has an identity, the identity start value and seed, table indexes, and quite a few other things are just not represented in information_schema, so writing code that mixes sys.* views and information_schema.* views didn't make sense.
Lowell
July 11, 2012 at 11:44 am
Probably for the same reason I do, the sysstem views have more information available than the INFORMATION_SCHEMA views. If I was writing something that had to be portable between different RDBM systems and I could rely completely on the information from those views then I would use them. As I am working solely with SQL Server, I'll use the system views directly and worry about changes to them as needed.
July 11, 2012 at 11:49 am
Lowell, Ah. Interesting. I didn't think about it that way.
FYI: Please don't think I was saying "it's bad to use sys.columns." I was just genuinely curious why people would still stick to a system view that is slated for future deprecation (supposedly).
July 11, 2012 at 11:54 am
Brandie Tarvin (7/11/2012)
Lowell, Ah. Interesting. I didn't think about it that way.FYI: Please don't think I was saying "it's bad to use sys.columns." I was just genuinely curious why people would still stick to a system view that is slated for future deprecation (supposedly).
Brandie, The system view sys.columns isn't being depreciated. What is being depreciated is syscolumns which is the system table for backward compatibility to SQL Server 2000.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply