When I run exec sp_tables it give me a "list" of tables in databases. In fact the result is a table where one of the columns contains the database table names. It is no problem to access it in a client application but I need to access that column and verify existance of a certain table in SQL Server's user stored procedure. So far anything I tried did not work. FETCH pertains to a cursor and I cannot declare a cursor for this command. The only thing I got so far was @.@.ROWCOUNT but it is of no use for me. Perhaps there is another way to verify if a table exists from inside a stored procedure.
I have the same problem in terms of verifying existence of a user stored procedure from inside another user stored procedure but if I get help on the first point, this one will be no brainer.
Thanks.
WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Jens K. Suessmeyer wrote:
WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ? HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hi Jens,
I do use INFORMATION_SCHEMA in my C# app but now I need to get this information in a stored procedure in SQL Server itself. Everything is confined to the same database boundary. The problem is: I am trying to optimize some operations in terms of performance and disk space. My records are first written in a common table. There might be a up to 100,000 records per day or more. All records are dated of course, so I can pull records for one day at will. All records for a day may be broken into up to 20 categories (symbols). They have a column (field) called symbol, After accumulating records for a few days, let say 5, I want to select all records for the latest date and insert them into a SEPARATE tables for each symbol. Then these records will be purged from the common table for good. The symbol column is absent in those tables but the symbol is part of their name, e.g "table_MSFT" Now, I do not want to create those tables in advance since it may lead to confusion. I want my stored procedure to be able to create a table once it found that a new symbol just appeared in the first, common table. It may be tomorrow or two months from now.
The reason I try to implement a design like this is that if I have to create tables half a year from now it will be hard to recall all the details and the errors will be inevitable. I want to invest time now and make it automatic for all time.
I actually solved the problem in terms of what I've tried to achieve. I do not know why it did not work for me the first time around, some goofy bug got on the way perhaps, but now it works very well. The way I do it is as follows:
exec sp_tables <tableName> This gives me just one row. I check @.@.ROWCOUNT and if it is 0, the table is created on the fly. I have done it many times already.
HOWEVER, I really want to get access to the columns in that row (or rows if I use exec sp_tables with no argument). I want to be able to access the fields and test the values if I need it. I know I will need it in the future. I am wondering if you are aware of a simple way to accomplish it.
If you do not know how to do it or it is not feasible to do, I want to consider CLR stored procedures. In other words I can write a C# code that will do it and store it in SQL Server. I have never done it and have a vague understanding how they work, how they are stored and how the information is retrieved from them. Remember, I want to use those stored procedures inside other stored procedures on the Server. In the client app it is all very simple.
Many thanks for your help.
|||
WHat abou tusing the INFORMATION_SCHEMA.Tables view ? I did not get what you are trying to achieve. If the tables are listed there, they are existing on the database. Or do you want to check another database for the tables ? HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
OK Jens,
This is the idea I have decided to try. What if I take a system stored procedure sys.sp_stored_procedures, change its name and modify it in such a way that it will store resulting table of the existing stored procedures either in a temporary table or even in a special permanent workbench table created specifically for this purpose. I had to change the name of the procedure to dbo.mine_sp_stored_procedures because otherwise I was getting an error on the first line but then the things proceeded smoothly (seemingly) in terms of its being executed with no errors.
However, when I tried to run it with parameters or without it broke down because it could not find certain objects. One of them is sys.numbered_procedures$. It is on line 118. The line numbering I found meaningless. The line number you get in the messages window does no correspond to anything in the code and if you try to find this line with Edit-->Go TO you will never find what you want.
Anyway, my knowledge base is too week at this point to find a way around it, how to handle it. Perhaps you may have an idea.
Thanks.
|||Hi Alex,
I don′t know if I got you right, but here is an option for getting the results from a stored procedure and looping through the details:
IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL
Drop Table #SomeTable
CREATE TABLE #SomeTable
(
Ident INT IDENTITY(1,1),
A int,
B varchar(60),
C varchar(255)
)
DECLARE @.I INT
DECLARE @.ROWCOUNT INT
SET @.I = 0
INSERT INTO #SomeTable
(
A,B,C
)
EXEC SP_Server_info
SET @.RowCount = @.@.ROWCOUNT
WHILE @.I<= @.RowCount
BEGIN
Select * From #SomeTable
Where Ident = @.I
SET @.I = @.I +1
END
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hi Alex, I don′t know if I got you right, but here is an option for getting the results from a stored procedure and looping through the details:
IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL
..........................................
HTH, Jens K. Suessmeyer.http://www.sqlserver2005.de
I just tried it. Thanks you very much. It worked. It looks like it is what I may use. I think this is the answer. So much for mine_stored_procedures:) But it is a good news.
No comments:
Post a Comment