Get Column name & table name from value
-
I think this might help:
-
deprecation?
last i heard, the views were there to stay, but could be changed;
some article i read said any changes would be things like adding columns to the end of the current views, but not outright changing or dropping them.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (7/11/2012)
deprecation?last i heard, the views were there to stay, but could be changed;
some article i read said any changes would be things like adding columns to the end of the current views, but not outright changing or dropping them.
As Lynn said, I'm misremembering what is actually deprecated. SQL Server Deprecated Items List. Syscolumns is indeed on there. For some reason, my brain just stuck the period in there because ... well, I can't explain that. @=)
Thanks for the other link, Lynn.
-
Excellent Code, thanks Lowell 🙂
When i ran sp_UglySearch proc I did not get ColName.
use this
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',' + @COLNAME + ','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
instead of
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,'''','''''') + ''') ;'
-
Mahmood, would you please bold the places where you made chances to the code to make it easier to read?
-
use this
SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',' + @COLNAME + ','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
instead of
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,'''','''''') + ''') ;'
-
both catalog view(sys schema) and information_schema views can be used.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
lokeshvij (7/18/2012)
both catalog view(sys schema) and information_schema views can be used.That works for the database. If I remember correctly, the OP is talking about a label box on a form in Visual Studio. That's not quite so easy.
-
Oh...got is Thanks!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply