August 17, 2017 at 8:45 am
dmeissner - Thursday, August 17, 2017 7:48 AMListen guys, I apologize for getting uptight. Maybe you guys do things different here by laying out the whole playing field when you post code, but the snippet I provided is only a snippet and is not intended to define a complete data schema with which a user can test. It is designed to simply show how a procedure in one database can search for an object in another. There is no specific schema to validate or verify or otherwise check as anything that relates to the database being searched comes in the form of parameters. The procedure knows nothing else except that which is passed to it. I do not even know if this works in any recent versions of MS SQL, but I am sure that any developer can take it and run with it to find out the exact syntax that will work for the version they are using.The procedure can be dressed up however anyone likes, but that is not the point of the snippet. No one needs a tutorial on how to create tables and load data into them. No tutorial on how to validate the incoming parameters or make sure that the database even exists. Anyone can add that however they like. This is designed to check for something and return a message if it does not exist. And I believe that I mentioned that it was a crude example. That would mean that it has a lot of room for improvement. It is not, as I said, a Mona Lisa.
This is designed for a seasoned programmer that does not need the whole landscape included to obfuscate the point being made. It should be a foregone conclusion that if any developer is using this, they are already extremely familiar with the databases they are working with and can interpret the snippet well enough to apply it to their schema. Any schema verification should have already been performed. This is not for a newbie. If a newbie uses it, (s)he will most likely run into issues that have to be troubleshooted, but that too is not the point being made.
The procedure is in no way altering any schema or data on the referring database either. It is a simple check for an object and I purposely did not include a bunch of other material that will confuse the matter. I simply wanted to show exactly the parts that perform exactly the need. That is it. I already said that if anyone has anything that they would like to contribute, they should feel free to post it. Lynn, that means if you think a schema should be provided, you have the right to post that. If someone can use it...great.
The biggest issue I have here is that up to now, neither of you have posted anything but criticism, because you were both offended by sarcasm that was not even directed at you. Read the whole thread again from my first post and consider my sarcasm and look at how you responded. And then to have Lynn tell me that he knows how he would fix it but wanted to see if I knew. Really? Honestly, I gave up that BS in 2nd grade. I am sorry, but read it again. How productive is that.
We have gone back and forth for days about semantics. Has there been any fruit produced from it? None whatsoever. This whole discussion has contributed absolutely nothing to the reader looking for a solution. If this were a work environment we would have been fired for wasting time.
Listen, I hope you can understand where I am coming from. I am not attacking you. I am simply trying to keep it simple. I wish you both the best. If you have anything (schema included) to contribute I will check it out. Other than that, I have spent too much time on this thread already. Life goes on and I prefer to spend my free time with my son who is having a difficult time with his.
Take care.
They were pointing out the very simply and basic fact that schema is a fundamental part of SQL server and you ignored it.
August 17, 2017 at 9:11 am
Okay, call me out. This is also a rough example based solely on your procedure. it is not what I would do in a production environment as it is missing comments, testing for the existence of the database in question, doesn't do error trapping. It is written (hopefully) so it could run in a SQL Server 2000 environment. If I was writing this for SQL Server 2005 or newer I would not use dbo.sysobjects, I would use sys.tables for checking for user tables and sys.columns for the columns, and I would also use sys.schemas for identifying the schema of any tables.
I have written this to minimize SQL Injection to the code as well. I also restricted the query to only user tables in sysobjects.
I am sure I missed some things in the writing of this procedure and I did execute it against my DBAUtilities database from my Sandbox database. The first execution of the procedure returned 'Valid Object' and the second execution returned 'Invalid Object'.
Does this satisfy your call out?
USE [SandBox];
GO
/*==================================================
Stored procedure from procedure database
==================================================*/
CREATE PROCEDURE dbo.ColumnExists(
@DBName NVARCHAR(256),
@SchemaName NVARCHAR(256),
@TableName NVARCHAR(256),
@ColumnName NVARCHAR(256)
) AS
DECLARE @SQLCmd NVARCHAR(MAX),
@ExecSQLCmd NVARCHAR(MAX),
@SQLParm NVARCHAR(MAX),
@Result VARCHAR(30);
SET @SQLParm = N'@inTableName nvarchar(256), @inSchemaName nvarchar(256), @inColumnName nvarchar(256), @outResult varchar(30) output';
SET @SQLCmd = N'
SELECT @outResult = ''Valid Object''
FROM
[@DBName@].[dbo].[sysobjects] AS [so]
INNER JOIN [@DBName@].[dbo].[syscolumns] AS sc
ON [sc].[id] = [so].[id]
WHERE
[so].[name] = @inTableName
AND [sc].[name] = @inColumnName
AND [so].[uid] = SCHEMA_ID(@inSchemaName)
AND [so].[xtype] = ''U'';
';
SET @ExecSQLCmd = REPLACE(@SQLCmd,'@DBName@',@DBName);
EXEC sp_executesql @ExecSQLCmd, @SQLParm, @inTableName = @TableName, @inSchemaName = @SchemaName, @inColumnName = @ColumnName, @outResult = @Result OUTPUT;
SELECT ISNULL(@Result,'Invalid Object');
GO
EXEC [dbo].[ColumnExists] @DBName = N'DBAUtilities', @SchemaName = N'dbo', @TableName = N'ColumnStandards', @ColumnName = N'columnname';
EXEC [dbo].[ColumnExists] @DBName = N'DBAUtilities', @SchemaName = N'dbo', @TableName = N'ColumnStandards', @ColumnName = N'schemaname';
GO
August 17, 2017 at 11:45 am
dmeissner - Thursday, August 17, 2017 7:48 AMListen guys, I apologize for getting uptight. Maybe you guys do things different here by laying out the whole playing field when you post code, but the snippet I provided is only a snippet and is not intended to define a complete data schema with which a user can test. It is designed to simply show how a procedure in one database can search for an object in another. There is no specific schema to validate or verify or otherwise check as anything that relates to the database being searched comes in the form of parameters. The procedure knows nothing else except that which is passed to it. I do not even know if this works in any recent versions of MS SQL, but I am sure that any developer can take it and run with it to find out the exact syntax that will work for the version they are using.The procedure can be dressed up however anyone likes, but that is not the point of the snippet. No one needs a tutorial on how to create tables and load data into them. No tutorial on how to validate the incoming parameters or make sure that the database even exists. Anyone can add that however they like. This is designed to check for something and return a message if it does not exist. And I believe that I mentioned that it was a crude example. That would mean that it has a lot of room for improvement. It is not, as I said, a Mona Lisa.
This is designed for a seasoned programmer that does not need the whole landscape included to obfuscate the point being made. It should be a foregone conclusion that if any developer is using this, they are already extremely familiar with the databases they are working with and can interpret the snippet well enough to apply it to their schema. Any schema verification should have already been performed. This is not for a newbie. If a newbie uses it, (s)he will most likely run into issues that have to be troubleshooted, but that too is not the point being made.
The procedure is in no way altering any schema or data on the referring database either. It is a simple check for an object and I purposely did not include a bunch of other material that will confuse the matter. I simply wanted to show exactly the parts that perform exactly the need. That is it. I already said that if anyone has anything that they would like to contribute, they should feel free to post it. Lynn, that means if you think a schema should be provided, you have the right to post that. If someone can use it...great.
The biggest issue I have here is that up to now, neither of you have posted anything but criticism, because you were both offended by sarcasm that was not even directed at you. Read the whole thread again from my first post and consider my sarcasm and look at how you responded. And then to have Lynn tell me that he knows how he would fix it but wanted to see if I knew. Really? Honestly, I gave up that BS in 2nd grade. I am sorry, but read it again. How productive is that.
We have gone back and forth for days about semantics. Has there been any fruit produced from it? None whatsoever. This whole discussion has contributed absolutely nothing to the reader looking for a solution. If this were a work environment we would have been fired for wasting time.
Listen, I hope you can understand where I am coming from. I am not attacking you. I am simply trying to keep it simple. I wish you both the best. If you have anything (schema included) to contribute I will check it out. Other than that, I have spent too much time on this thread already. Life goes on and I prefer to spend my free time with my son who is having a difficult time with his.
Take care.
Actually, I was one of the respondents on the original set of 4 posts so they were directed at me. They were also directed at people that, unlike you so far, have made a difference for people seeking help. I found your comments to be totally unnecessary, uninformed, more useless than the posts that lit your fuse, and highly offensive.
The semantics that we're discussing are how "simple" code can "kill" in the wrong hands and, since this is the internet, it will get into the wrong hands. If this were a work environment, you're correct about someone being fired but it wouldn't be the people that are trying to show you a better way. They'd have already rewritten it correctly.
--Jeff Moden
August 17, 2017 at 12:02 pm
@Lynn,
Consider the use of QUOTENAME in your REPLACE to help mitigate SQL Injection even when you use hard-coded brackets.
--Jeff Moden
August 17, 2017 at 1:56 pm
Jeff Moden - Thursday, August 17, 2017 12:02 PM@Lynn,
Consider the use of QUOTENAME in your REPLACE to help mitigate SQL Injection even when you use hard-coded brackets.
Okay, I will try to remember that. I do this a fair amount here as I find myself writing quite a bit of dynamic SQL. As I know the source of the data being used I am comfortable with how I have been doing it, but you are correct in that others may use my code in ways I wasn't intending. There are a few people here who think they know what they are doing who shouldn't be allowed to write code without direct supervision. Sorry, but I seem to have to rewrite some peoples code more often than I should.
August 21, 2017 at 10:48 am
So, I post code because I get called out and he goes all crickets on us.
August 22, 2017 at 5:31 am
Lynn Pettis - Monday, August 21, 2017 10:48 AMSo, I post code because I get called out and he goes all crickets on us.
Is that a bad thing?
Viewing 7 posts - 91 through 96 (of 96 total)
You must be logged in to reply to this topic. Login to reply