|
Niladri Biswas
|
|
|
|
|
I have a few questions to ask. Before that let me tell you my problem definition.
I want to create some tables and insert them with some default data when ever a new database is created in my sql server 2005. So i was hoping if there whould be any way to add a trigger on sys.databases table.
So these are the questions I have:
1. How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?
We can query these tables but they are not visible under any database(master or msdb).
2. How to add a trigger on sys.databases or sysobjects table ?
3. How to list all triggers for a particular table?
s.a.w.
|
|
|
|
|
Hi Sunit
I am presenting the answers of your questions
1) a)How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?<br />
Ans:
SELECT * FROM SYS.DATABASES
SELECT * FROM SYS.OBJECTS
b)We can query these tables but they are not visible under any database(master or msdb).
Ans:SQL SERVER stores its system objects in a read-only database call RESOURCE DATABASE. However,they appear logically as SYS schema for every database. It can only be modified by Microsoft Customer Support Services (CSS) specialist.
For more information, please visit http://msdn.microsoft.com/en-us/library/ms178028.aspx[^]
However, SYSOBJECTS is just a view(the following query bears the proof) . So you cannot modify the underlying system table.
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
Output:
<code>CREATE VIEW sys.objects</code> AS SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published FROM sys.objects$
2) How to add a trigger on sys.databases or sysobjects table ?
Ans:
If I query the SYSOBJECT / SYS.DATABASE, I will get all the SYSTEM TABLES + USER TABLES / SYSTEM DATABASES + USER DATABASES respectively.
So I can easily write a trigger on them.
e.g.In the MASTER database, there is a system table call spt_fallback_dev.
I have fired the following trigger statement & it worked
CREATE TRIGGER SOME_TRIGGER ON spt_fallback_dev
FOR UPDATE AS
SELECT * FROM spt_fallback_dev
The same holds good for any user table too.
For more information , you can look into this http://www.sql-server-performance.com/faq/trigger_system_table_p1.aspx[^]
3) How to list all triggers for a particular table?
Ans:Lets do a practical
Create a table like this
CREATE TABLE SAMPLE_TABLE
(
EID INT,
ENAME VARCHAR (12)
);
Add 2 triggers like the following
Trigger1
CREATE TRIGGER SAMPLE_TRIGGER_A ON SAMPLE_TABLE
FOR UPDATE AS
SELECT * FROM SAMPLE_TABLE
Trigger2
CREATE TRIGGER SAMPLE_TRIGGER_B ON SAMPLE_TABLE
FOR UPDATE AS
SELECT * FROM SAMPLE_TABLE
Now execute the following query
SELECT NAME
FROM SYSOBJECTS
WHERE DELTRIG = (
SELECT ID
FROM SYSOBJECTS
WHERE NAME = 'SAMPLE_TABLE')
OUTPUT:
NAME
------------------
SAMPLE_TRIGGER_A
SAMPLE_TRIGGER_B
Hope this helps.
Niladri Biswas
modified on Saturday, July 4, 2009 12:17 AM
|
|
|
|
|
|
Hi Guys,
I am using Datareader in my C# code to get data from db and dumping it into excel sheets. I am able to do it for everyrow correctly (based on the data from the sql view).
I have a problem in one of the sql views.
rolename roledesc roletype roleuser
----------------------------------------------------------------------
Team Leader (required) xyzxyz Primary Mike
Team Leader (required) xyzxyz Backup Cary
Process Focal abcabc Primary Jeff
Process Focal abcabc Backup Dave
As, when i read each row of this using reader i get,
(Team Leader (required) xyzxyz Primary Mike) AND (Team Leader (required) xyzxyz Backup Cary )) in 2 separate rows.
But when i have to dump this data to excel sheet, i have to only dump the Team Leader (required) once and the roleuser should be Mike and next to him Cary.
I mean,
cell1 cell2 cell3 cell4
----------------------------------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
Please help me in modifying the sql view for this.
Tables: rolename, roledesc comes from roledescription table, roletype comes from roletypedescription table, roleuser from username table,
I can explain if the above statements are not clear
Thanks Ramm
|
|
|
|
|
My SP reads as
ALTER PROCEDURE [dbo].[tblRoleSummary_sel_all_sp]
SELECT txtRoleName, txtRoleDescription, txtRoleTypeDescription, txtUserName FROM tblRoleSummary_View
Do I need to change the SP and display the data as per my requiremnt or view itself can be changed for this/?
Thank YOu
Ramm
|
|
|
|
|
Do a self-join on the table, something like:
select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'
I think that's right, off the top of my head.
This assumes that every Primary has a Backup. If not, you will need an outer join.
|
|
|
|
|
Hi,
Its not compulsory, for every Primary, a backup will be available,
My query is
Hi, This is my SQL Query
SELECT dbo.tblRoleDescription.txtRoleName, dbo.tblRoleDescription.txtRoleDescription, dbo.tblRoleType.txtRoleTypeDescription, dbo.tblUserInfo.txtUserName, dbo.tblRoleAssignment.idSubProject, dbo.tblRoleAssignment.idLaunch FROM dbo.tblLaunchInfo INNER JOIN dbo.tblRoleAssignment ON dbo.tblLaunchInfo.idLaunch = dbo.tblRoleAssignment.idLaunch INNER JOIN dbo.tblProjectUsers ON dbo.tblRoleAssignment.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblRoleAssignment.idUser = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblUserInfo ON dbo.tblProjectUsers.idUser = dbo.tblUserInfo.idUser INNER JOIN dbo.tblRoleDescription ON dbo.tblRoleAssignment.idRole = dbo.tblRoleDescription.idRole INNER JOIN dbo.tblRoleType ON dbo.tblRoleAssignment.idRoleType = dbo.tblRoleType.idRoleType
tblroleassignment -> idSubProject , idUser , idRoleType, idRole , idLaunch tblroledescription -> idRole(PK), txtRoleName, txtRoleDescription tblRoleType -> idRoleType (PK) , txtRoleTypeDescription tbluserinfo -> iduser, txtusername tbllaunchinfo -> idlaunch tblprojusers -> idsubproject, iduser
the sql query and the tables are given above, Please help me
Thank you,
Ramm
|
|
|
|
|
To be completely honest, I don't have time to write your query for you. I have my own work to do.
I've given you an idea of how to do it: a self-join on the table to link the Primary user to the associated Backup user will give you what you want.
If you want to simplify things for yourself you could create a view that only includes Primary users. Then create another view that only includes Backup users. Then join them together on role id. That would make the query easier to work with.
|
|
|
|
|
Hi Experts
I am using sql server 2005 express edition
i am not able to run the full text search.
when i create the catalog or any query realted to Full Text Search
it give me message
 Full-Text Search is not installed, or a full-text component cannot be loaded.
plz help me how the full text search is runing
Thank u
Dinesh
|
|
|
|
|
|
Hello,
I have two columns, a category and a value. Below is the data:
| Category | | Total | | Devices | | 38111 | | Plant | | 6063 | | Improvements | | 2266 |
I want to add two more columns: Zero and Sum
So the columns would be Category, Zero, Total, Sum
Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total?
The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time.
JM
I would be forever indebt to you if you could help out. Thanks!
|
|
|
|
|
After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table.
The TEMP table would be something like this:
Category Zero Total Sum
Devices 0 38111 38111
Plan 38111 6063 44147
Improvements 44174 2266 4640
Use the following code snipet to get you going with the logic ...
DECLARE myCURSOR CURSOR FOR
SELECT CATEGORY, TOTAL
FROM myTABLE
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
SET @PBAL = 0.0
WHILE(@@FETCH_STATUS=0) BEGIN
SET @GTOTAL = @PBAL + @XTOTAL
SET @MYSQL = 'INSERT INTO #MYTEMP'
SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
SET @MYSQL = @MYSQL + @GTOTAL + ')'
PRINT @MYSQL
EXEC(@MYSQL)
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
END
CLOSE myCURSOR
DEALLOCATE myCURSOR
GO
Hope this helps you !
|
|
|
|
|
I will give that a shot, thank you for your help!
|
|
|
|
|
I am trying to update records in a table in sequence by setting up a Cursor and using Fetch like so..
DECLARE VPres_Cursor CURSOR FOR
select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID
OPEN VPres_Cursor;
FETCH NEXT FROM VPres_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM VPres_Cursor;
update dbo.VicePresident
set dbo.VicePresident.LastName = LastName
END;
CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;
While I am able to see the records in the Results window in SQL Management Studio, I am not able to see the actual update in the table.
47 Burr
48 Clinton
...
..
81 Humphrey
Can anyone help??
Thanks in advance..
Gymnast.
|
|
|
|
|
Do like this
DECLARE @LASTNAME VARCHAR(50)
DECLARE VPres_Cursor CURSOR FOR
select dbo.Person.PersonID, dbo.Person.LastName from dbo.Person inner join dbo.VicePresident
ON dbo.Person.PersonID = dbo.VicePresident.PersonID
OPEN VPres_Cursor;
FETCH VPres_Cursor INTO @LASTNAME
WHILE @@FETCH_STATUS = 0
BEGIN
update dbo.VicePresident
set dbo.VicePresident.LastName = @LASTNAME
FETCH VPres_Cursor INTO @LASTNAME
END;
CLOSE VPres_Cursor;
DEALLOCATE VPres_Cursor;
I have only highlighted those lines which I think to be changed.
And it is working fine in my system.
Hope this helps.
Let me know in case of any concern
Niladri Biswas
modified on Thursday, July 2, 2009 2:45 AM
|
|
|
|
|
Using the update in a cursor I thought you needed Where Current of cursorname otherwise you set all the names to @Lastname
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Mycroft,
Actually I was not able to fathom your statement.
Did you mean that, the update will be based on some condition!
But actually, in the original post, the author of the code didn't specify any such thing by which I can use the Where Current of cursorname.
So I thought that I should do that in the way I did!.
You are always welcome to give a better solution so that I can also improve myself.
Niladri Biswas
|
|
|
|
|
When you do an update on a table if you do not apply a filter you will update every name to the variable. This updates the current record being accessed by the cursor.
Declare csrPV Cursor For
SELECT ColName_0
FROM stg.IRsCap
WHERE ID > @HeadRow
OPEN csrPV
FETCH NEXT FROM csrPV INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Value != ''
SET @LastValue = @Value
IF @Value = ''
BEGIN
UPDATE STG.IRsCap SET ColName_0 = @LastValue
WHERE CURRENT OF csrPV
END
FETCH NEXT FROM csrPV INTO @Value
END
CLOSE csrPV
DEALLOCATE csrPV
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
Thanks Mycroft! But I am yet to understand portions of the code you suggested:
SELECT ColName_0
FROM stg.IRsCap
I have a resultSet from a join that I would liek to use.. I assume I can still use that.
WHERE ID > @HeadRow
I assume that @HEADERROW is the value of the first IDin the table I am updating (which is 33 in my case).
IF @Value != ''
SET @LastValue = @Value
Here I assume that @Value is declared to hold the Value of the LastName
Your input is appreciated.
Thanks,
Rosh
|
|
|
|
|
Hello friends I have ha table like below
Amount GLID
2000 6523
4000 2356
4500 5623
I need to write a query that will show this table like below
6523 2356 5623
2000 4000 4500
How can I do this? Any help would be helpful.
Thanking in advance
Johnny
|
|
|
|
|
I believe what you need is PIVOT[^]
only two letters away from being an asset
|
|
|
|
|
|
WRONG - you can use MAX or MIN as the aggregate when you want to pivot on 1 value. It is still using an aggregate but it allws you to achieve a solution.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
