Where To Gain The Information About Sp_MSforeachtable .....
Sep 26, 2000Hi,
If anybody know the source (links) with information about SP like this one.
sp_MSdependencies and so on.
thanks.
ADVERTISEMENT
Why The Big Performance Gain?
Dec 10, 2007Hi All
I am trying to figure out why i recieved such a performance gain by separating a sproc into two separate transactions (data integrity is still ok)
Before when it was 1 tran, my proc was flowing something like this:
BEGIN TRAN
INSERT into work tables (2 cursors and 1 straight insert)
INSERT into data warehouse
FROM joins to work tables and other transactional tables.
COMMIT TRAN
now it is:
BEGIN TRAN
INSERT into work tables (2 cursors and 1 straight insert)
COMMIT TRAN
BEGIN TRAN
INSERT into data warehouse
FROM joins to work tables and other transactional tables.
COMMIT TRAN
The first sproc was running in 18 min and I was constantly seeing the spid blocked (and sleeping) by the same spid.
Now when using two separate transactions it runs in a minute and a half.
What gives? I am dying to know the explaination so i can plan better in the future. This is running on SQL 2000.
Thanks!
Cam
Sp_msforeachtable
Jan 23, 2008Hi y'all,
I found following sql statement:
How is it possible to make this general for a certain database? So i can get this information with a project parameter so i don't need to store this stored procedure in each single database?
CREATE TABLE #temp ( table_name sysname , row_count int, reserved_size varchar(50), data_size varchar(50), index_size varchar(50), unused_size varchar(50))SET NOCOUNT ONINSERT #tempEXEC sp_msforeachtable 'sp_spaceused ''?'''SELECT a.table_name, a.row_count, count(*) as col_count, a.data_sizeFROM #temp aINNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate database_defaultGROUP BY a.table_name, a.row_count, a.data_sizeORDER BY CAST(Replace(a.data_size, ' KB', '') as integer) descDROP TABLE #temp
Thanks!
Sp_msforeachtable Problem.
Apr 26, 2008hi
i want to retrieve all columns of all tables by this code :
exec sp_msforeachtable 'exec sp_columns ''?'''
but when i execute this script, sql server return number of empty result set, how to solve this problem ?
thanks.
Performance Gain On Specifying SqlDbType On Parameter
Jun 28, 2004How much if any of a perf gain is there when you specify the SqlDbType on a parameter?
View 1 Replies View RelatedBest Book To Gain Good Knowledge In DBA
Feb 26, 2007Dear Friends,
please suggest me the best book to gain sound knowledge in DBA concepts as well as
writing in sub queries, complex queries as well as inner queries.
thank you very much
Vinod
Gain Access To Locked Server
Jun 19, 2007Am I imagining it or did I read somewhere once about an tool/command/method that allows the administrator/dba to access the server even if no-one else can - probably only in certain scenarios.
I think it had a use for a situation where all the connections are used up and no further connection pooling can kick in for a while. This tool enabled the dba to still get to the server and I presume still be able to run stuff like sp_who and kill.
Does this ring a bell with anyone?
EXEC Sp_msforeachtable 'sp_spaceused ''?'''
Feb 25, 2008What does the following SQL code mean?
EXEC sp_msforeachtable 'sp_spaceused ''?'''
Syntax Problems W/ 'sp_MSforeachtable'
Jun 6, 2003I'm having trouble getting the syntax of this snippet of code to work. The big issue is with
the SP 'sp_MSforeachtable' using the cmd 'BDCC DBREINDEX'...
-- Declare the variables.
DECLARE @database varchar(75)
Declare @str varchar(1000)
Set @database = 'pubs'
SELECT @str = 'EXEC ' + @database + '.dbo.sp_MSforeachtable @command1=''DBCC DBREINDEX (''''?'''')''' + Char(13)
print @str
exec @str
/* results are:
EXEC pubs.dbo.sp_MSforeachtable @command1='DBCC DBREINDEX (''?'')'
Server: Msg 911, Level 16, State 1, Line 7
Could not locate entry in sysdatabases for database 'EXEC pubs'. No entry found with that name. Make sure that the name is entered correctly.
*/
Sp_msforeachtable In A DTS Package With Global Variables
Feb 27, 2001Hi,
what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.
To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.
This is the code in the Execute SQL Task:
sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"
This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.
Finally I've written a cursor to the job which looks like this:
DECLARE @tablename varchar(35),
@loadnumber int
SELECT @loadnumber = ?
DECLARE delete_cursor CURSOR FOR
select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')
OPEN delete_cursor
FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)
FETCH NEXT FROM delete_cursor INTO @tablename
END
CLOSE delete_cursor
DEALLOCATE delete_cursor
But this code doesn't compile either...
Any suggestions on how to get the code working within DTS?
Stef
SecurityException Class SQLServerDatabaseMetaData's Signer Information Does Not Match Signer Information Of Other
May 23, 2007Hello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
Information About A Job After It Ran
Jul 2, 2001I have a job that runs each night that deletes all records from a table that are older than two weeks old. Is there some way I can view how many records were deleted from the table each night. Is it written to a log somewhere? Thanks in advance!
View 1 Replies View RelatedGetting Information
Aug 4, 2005Hi all,
This is my first thread here.
I got an information about
SET NO_BROWSETABLE ON/OFF
in your forum.. The replier told that this is an undocumented option.
My question is how you guys find information on such undocumented things....
Thanks in advance.
Saint
Information !
Mar 26, 2008Hello,
Can anyone clarify the nuances of How to deal with the change management environment in SQL Server.
Thanks.
Get Job Information
Sep 6, 2007I am trying to get job information and have a query set up. However, I would also like to get the owner information. However, the job only stores the sid of the owner. Does anybody know how I could change this query to get the owner name instead of sid? Don't want to use the sp_help_job command.
SELECT @@SERVERNAME AS Server, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description, msdb.dbo.sysjobs.notify_level_eventlog, msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend, msdb.dbo.sysjobs.notify_level_page, msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created, msdb.dbo.syscategories.name AS Category_Name, msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time, msdb.dbo.sysjobservers.last_run_outcome, msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date, msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator, msdb.dbo.sysoperators.email_address, msdb.dbo.sysjobs.date_modified, GETDATE()
AS Package_run_date
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
Information Being Cut Off
Mar 17, 2008I have a report where the information is getting cut off the report.
At the beginning of or at the ending of the page it is dropping the information.
My layout setting are set 8.5in wide by 11in height. .5in for either left or right margin. 1in for top and bottom.
I run the report and I'm missing a record.
I adjust the height to 10in to force diffferent page breaks and it's there.
I have the setting in one of the groups set for a page break at end. If I uncheck that it doesn't help.Still no records.
What is going on?
Looking For Information
Mar 15, 2006I'm looking for books or information on command line configuration of settings for things like activating Remote Connections under the Service Area Configuration and setting a port for that connection.
What books would be good to get or where can I download papers on this?
Any help would be appreciated.
Locking Information
Dec 23, 2005Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
Trigger Information
Jul 26, 2001Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep
Last Login Information
Aug 30, 2001I am doing an audit of user information in our database and was wondering if there is a way for me to see the last time a user logged into the database. I am using a SQL Server 7.0 box. Any Information would be greatly appreciated. Thanks!
View 1 Replies View RelatedAlias Information In 70
Jun 27, 2000Hi,
In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.
username login name database
-------- ----------- --------
aaaa NULL XYZ
NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use
sp_dropuser 'null'
i think guest user will also drop from XYZ database.
So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7.
Pls suggest me regarding this.
thank u
raj
HELP (URGENT) Need Information!
Sep 26, 2000Hi,
I administering SQL server and one of or Db (size ~7 Gb) have a table with size 396 MB and 25 indexes created on this one. We working with this DB using Siebel Call center.
I have met a very strange situation: when I tried to get some data (using Siebel) my request take ~5 min. I run Profiler and catched this T-SQL statement which Siebel send to SQL server. After that, I run this T-SQL query using Query Analyser and found that Query Optimiser used wrong index. (clustered index) and try to compare every 470436 rows from the tables.
This is the statement. @P1, @P2, and @P3 just variable which siebel transfer to SQL server.
SELECT ........
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID
WHERE
((T1.APPT_REPT_FLG != @P1 OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != @P2 OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_DEFECT_ID = @P3)
But I found the very strange situation - when I manualy put the value '1-FRK' instead of @P3 - Query Optimizer used RIGHT index and I had a very good performance.
And addition when I tried to run the first Query on the same table but with the only 1000 rows the Query Optimiser choose right index.
Wat's on, wat happend with My Query Optimizer? Wy his decision so different from time to time?
Thank's a lot for any information about how to make Optimiser obedient.
Getting User Information
Jan 19, 2005I need to write an auditing trigger that will capture time of an insert and user that is making the insert. The time part i think i can use getdate(), but how can I find out which user is making the insert? Any help appreciated.
View 2 Replies View RelatedLogin Information
Oct 3, 2005Hi,
Can we retrieve the Login Failed and succedded for SQL Server?
I want to know the login failed and succedded information of SQL Server.
Can we retrieve the same?
Let me know ASAP.
Thanks and Regards,
Pramod
INFORMATION ON PROCESS ID
Nov 17, 1998Is there any way to use Transact SQL to get the detailed information on a process like you get when you use the GUI interface under the Enterprise manager for server activity.
Thanks!
Trigger Information
Jul 26, 2000Is there a way to view the triggers on a table using query analyzer?
I have a developer that wants to see this information without having to right click every table in the database and viewing the triggers through enterprise manager.
Thanks,
Brad
Logon Information
Feb 15, 2001I just started at a new company. The security stinks, everyone logs into the servers as sa (including many applications). I am taking steps to tighten security and restrict access to the sa login. I would like to capture some additional information at logon as an interim measure.
Can anyone assist me in automatically recording the following:
Host Name / IP Address
Login time
I realize I could periodically run an sp_who2 and capture the results - I also realize that if I turn on auditing, the logon will be written to the SQL Srvr LOG - I'm looking for an elegant / low impact way to capture the logon and host information.
Thanks in advance for any help.
Scott Wright
Need Help Getting Information From 2 Tables At Once
Jan 2, 2002I'm trying to access data from one table by matching ids pulled from another table. I can't quite get the syntax though if anybody could help I would appericate it.
Here is where I'm at
select * from table1 where variable 1 = (select distinct(variable 2) from table 2 where variable 3 = variable 4)
User Information!!
Jun 24, 2004Hi,
I'm trying to get the information about the user who modified the record in the table. I'm able to capture the datetime of modification through trigger.
CREATE TRIGGER [upd_dtm] ON dbo.Sample
FOR INSERT, UPDATE
AS
BEGIN
UPDATE Sample SET update_dtm = (SELECT GETDATE())
WHERE Sample.ID IN (SELECT ID FROM inserted)
END
I'm stuck, how to get the User who modified it?
Any help is greately appreciated!
Job Schedule Information
Nov 7, 2005Hi,
Does anyone has a script to find out what are the scheduled timining for all sql server agent jobs.
I'm trying to get the result with the following query, it doen't seem working..
select sj.name,
case sjs.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end as Frequency,
case sjs.freq_interval
when '1' then 'Sunday'
when '2' then 'Monday'
when '4' then 'Tuesday'
when '8' then 'Wednesday'
when '16' then 'Thursday'
when '32' then 'Friday'
when '64' then 'Saturday' end as DAY
from sysjobs sj, sysjobschedules sjs
where sj.job_id = sjs.job_id
Any help would be greately appreciated.
Thanks
Passing Information
Jun 21, 2007What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.
SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHEREcontact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)
Where Is The Transaction Log Information Kept?
Feb 27, 2004I'm assuming that in one of the MSDB tables I can find transaction log information (current size, amount of space used.. blah blah).. but I can't seem to figure out which one.. any help is appreciated.. SQL 7.0
Travis
Detailed Log Information
Apr 5, 2004We just started using SQL Server. I'm new on the SQL side of things. We have SQL setup in a test environment with users connecting and performing job related functions. An in house programmer has developed software for our users which connects to the SQL Server
I'm looking for information pertaining to events that have teken place within SQL. Does SQL give you details on updates and changes made to specific tables. I'm looking for some way of looking up item numbers and the user that entered the data. We have noticed that some of the users may be entering in wrong data within certain tables. And would like to educate them on what they are doing wrong.
I need to know what certain users are logging and entering into our SQL Server.
What are the most detailed logs that SQL Server provides that has information on what the users are doing has far an entering in data.