Yes. See sp_addextendedproperty in the Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>|||Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?|||Yes. See sp_addextendedproperty in the Books Online
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
>
>
Showing posts with label books. Show all posts
Showing posts with label books. Show all posts
Sunday, March 11, 2012
Saturday, February 25, 2012
add index
I know someone will refer me to online books, but I did a search on the
microsoft site and can't get to it.
I know the general format for adding an index is:
Alter Table tablename ADD INDEX indexname, type, FieldName
The thing is, I'm not sure what to put in for type? Do I use quotes? Can
someone actually give me an URL to an example that will show?
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1HockeyFan via webservertalk.com wrote:
> I know someone will refer me to online books, but I did a search on
> the microsoft site and can't get to it.
> I know the general format for adding an index is:
> Alter Table tablename ADD INDEX indexname, type, FieldName
> The thing is, I'm not sure what to put in for type? Do I use quotes?
> Can someone actually give me an URL to an example that will show?
Search for CREATE INDEX in BOL.
examples:
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
HTH,
Stijn Verrept|||>I know someone will refer me to online books, but I did a search on the
> microsoft site and can't get to it.
SQL 2000:
http://msdn.microsoft.com/library/e...portal_7ap1.asp
SQL 2005:
http://msdn.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx
> Alter Table tablename ADD INDEX indexname, type, FieldName
Actually, I'm not sure where you got that syntax, it's...
CREATE [UNIQUE] [NON|CLUSTERED] INDEX
indexname
ON tablename (columnName [DESC] [, ...]);
SQL 2000:
http://msdn.microsoft.com/library/e...create_64l4.asp
SQL 2005:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
microsoft site and can't get to it.
I know the general format for adding an index is:
Alter Table tablename ADD INDEX indexname, type, FieldName
The thing is, I'm not sure what to put in for type? Do I use quotes? Can
someone actually give me an URL to an example that will show?
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1HockeyFan via webservertalk.com wrote:
> I know someone will refer me to online books, but I did a search on
> the microsoft site and can't get to it.
> I know the general format for adding an index is:
> Alter Table tablename ADD INDEX indexname, type, FieldName
> The thing is, I'm not sure what to put in for type? Do I use quotes?
> Can someone actually give me an URL to an example that will show?
Search for CREATE INDEX in BOL.
examples:
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
HTH,
Stijn Verrept|||>I know someone will refer me to online books, but I did a search on the
> microsoft site and can't get to it.
SQL 2000:
http://msdn.microsoft.com/library/e...portal_7ap1.asp
SQL 2005:
http://msdn.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx
> Alter Table tablename ADD INDEX indexname, type, FieldName
Actually, I'm not sure where you got that syntax, it's...
CREATE [UNIQUE] [NON|CLUSTERED] INDEX
indexname
ON tablename (columnName [DESC] [, ...]);
SQL 2000:
http://msdn.microsoft.com/library/e...create_64l4.asp
SQL 2005:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
Thursday, February 16, 2012
add a login to a database role (was "Very Confusing")
This stuff makes no sense what so ever,
In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"
There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."
I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.
But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:
1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login
The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.
-PatP|||7. Add your junk Windows login to your junk role.
-PatP
1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.
I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!
-PatP|||No, the junk login does not appear when i click the add button of the junk role.
The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator
EXECUTE sp_helpuser @.cJunque -- Prove "no one home"
EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty
EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login
EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user
EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?
-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.
I dont think this can be done through Enterprise Manager directly??
:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.
(1 row(s) affected)
'piii\dbtemp' added to role 'Junque'.
(1 row(s) affected)
Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.
User has been dropped from current database.
In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"
There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."
I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.
But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:
1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login
The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.
-PatP|||7. Add your junk Windows login to your junk role.
-PatP
1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.
I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!
-PatP|||No, the junk login does not appear when i click the add button of the junk role.
The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator
EXECUTE sp_helpuser @.cJunque -- Prove "no one home"
EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty
EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login
EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user
EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?
-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.
I dont think this can be done through Enterprise Manager directly??
:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.
(1 row(s) affected)
'piii\dbtemp' added to role 'Junque'.
(1 row(s) affected)
Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.
User has been dropped from current database.
Subscribe to:
Posts (Atom)