March 12, 2012 at 3:02 pm
I've been using SQL Server, I think since SQL 6.5. Then we went to SQL 7, and then SQL 2000. We've been on SQL 2005 for a long time. So I've used SQL Server for a long time. I know that with SQL Server 2005 Microsoft introduced the concept of schemas. Before that, something like dbo was, well, I don't know what, I can't remember, but it wasn't a schema. Now it's a schema and it's possible to have multiple schemas in a database. I've seen things like the "sales" schema, etc. Well, due to some changes rapidly occurring here at work, I think I've got to move several queries that my boss wrote over the years to do his management reports, into SQL Server as stored procs, where I think it will be easier to automate these reports. We've already got lots of SP's, though, and I want to make it easier for anyone else to find these new SP's, so I'm thinking it would be a good idea to make up a new schema where I can create all of these new SP's under. The only thing is, how?
OK, here's the questions I need answered:
1) Who can create schemas?
2) How do you create schemas?
3) Is there some maximum length a schema may have?
4) Once the schemas are created, what permissions do I need to use, to apply to them, so that the average user can run them?
5) Suppose I were to create a schema called "ManagementReports" (not that I would, it's too long, I'm just using this as an illustration). Is it necessary to prepend all tables, views or oridinary SP's with "dbo", when I want to access all of our currently existing tables, views, SPs? Or is the dbo schema assumed?
I'm sorry to not be able to researched this more, however I'll soon be swamped with more responsibilities than I've had before, and won't have as much time to investigate. I don't want to do all of the management reports that my boss does, in the hugely labor intensive manner he does them in. (Opening each report in SSMS, editing the queries to put in new dates, and then running them.) I need the Cliff's Notes version on schemas, please.
Kindest Regards, Rod Connect with me on LinkedIn.
March 12, 2012 at 3:19 pm
This will explain far better than I can.
http://msdn.microsoft.com/en-us/library/ms190387.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply