Easy way to view DB

Hi,
I have several DB with 80+ tables in each. I would like to find out what is
the best method to learn and view the relationships of these tables. I made
a SQL diagram of all the tables but since there are so many tables, it is
not easy to view the 'big picture'. Does anyone have any suggestions or may
be a tool that shows DBs in a better view?
Thank you.Greg has a nice product that would serve you well here.
http://www.ag-software.com/ags_scribe_index.aspx
--
-oj
RAC & QALite!
http://www.rac4sql.net
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:%23doHPiayDHA.3772@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have several DB with 80+ tables in each. I would like to find out what
is
> the best method to learn and view the relationships of these tables. I
made
> a SQL diagram of all the tables but since there are so many tables, it is
> not easy to view the 'big picture'. Does anyone have any suggestions or
may
> be a tool that shows DBs in a better view?
> Thank you.
>

Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
--
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> > Is there a view or something that lets you see what recovery mode your
> > db is set to?
> >
> > I'm trying to tidy up a stored procedure that does a backup of all the
> > trans logs, but I get an error when it hits a db set to Simple.
> >
> > Thx.
> >

Easy T-SQL, brain lapse on Friday

Is there a view or something that lets you see what recovery mode your
db is set to?
I'm trying to tidy up a stored procedure that does a backup of all the
trans logs, but I get an error when it hits a db set to Simple.
Thx.> Is there a view or something that lets you see what recovery mode your
> db is set to?
Try:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Hope this helps.
Dan Guzman
SQL Server MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||One way is to use DATABASEPROPERTYEX with the recoverymode property.
Andrew J. Kelly SQL MVP
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...
> Is there a view or something that lets you see what recovery mode your
> db is set to?
> I'm trying to tidy up a stored procedure that does a backup of all the
> trans logs, but I get an error when it hits a db set to Simple.
> Thx.
>|||Thanks guys, worked like a charm.
Happy Friday!
Andrew J. Kelly wrote:[vbcol=seagreen]
> One way is to use DATABASEPROPERTYEX with the recoverymode property.
> --
> Andrew J. Kelly SQL MVP
> "PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
> news:1156508289.443880.30620@.h48g2000cwc.googlegroups.com...

Dynamic views

I havea archive of tables and i want to make a dynamic view for the tables. like a view that shows the last 90 days of data.
is that possible without runnning a script every day or sumthin of that sort?
plz helpI think this will do,

create view myview
as
select * from yourarchivetable where date < dateadd(day, -90, getdate())|||correction,

create view myview
as
select * from yourarchivetable where date > dateadd(day, -90, getdate|||that works ok if the archival table is known. but the archival tables are usally dated (ie name contains the date or year in it) in that case what do u do? the archival tables are monthly tables

say if the view wants last 90 days and today is the 12dec so the view should capture dec, nov, oct, and spet data.

now is this possible ?|||I think what you are looking for here is a partitioned view. It doesn't work exactly the way you want it to, but it should give you what you need with minimal administrative overhead and good performance.

Basically, each of your underlying tables needs to have a check contstraint applied (that should be easy, since you indicate in your post that they are grouped logically by month).

Then you create a view that unions together all the data from the various tables (I'm assuming that the table definitions are the same).

You could probably even figure out a way to programmatically update the view definition on the first day of each month to add the new table (and eliminate the oldest table if you're only going for 90 days).

The check constraints in the underlying tables are important for performance.

Check out Partitioned Views in SQL BOL.

hmscott|||Well rite now i have scripts running running as jobs periodically that keep the views updated for say fiscal year, fiscal calendar year, last 90 days, last 60 days etc.

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic view

Is it possible to create dynamic sql views in Sql Server 2005?

I have a requirement of having to change the Sql View depending on the user selection criteria.
Is it possible?

No but you can create a stored procedure with dynamic sql kludge

If you would change the view (with an alter view statement) what would happen when 2 users would hit the view at the same time (problem)

sp with dynamic sql is your best bet (probably including a temp table, I don't know your requirements so that's tough to answer)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks.

I cannot use stored procedure, any other option to programattically alter the view?|||What is your requirement that means that you have to "dynamically change the view"?|||

Krutika wrote:

Thanks.

I cannot use stored procedure, any other option to programattically alter the view?

No. Well, you can use CASE statements in your view if that helps.|||No, A "view" is always the same list of fields.

Also, if you create a view which says:

CREATE VIEW V_TABLEA AS
SELECT * FROM TABLEA

When you create the view, it caches the list of fields it is going to display. This means if you add or delete a field from TABLEA, these changes will not show in the view. You need to recreate or alter the view to get a new field list.

Your only option is a stored procedure.

Dynamic View

Dear All,
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 6 Jun 2006 06:37:44 -0700, jumpa wrote:

>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin

Dynamic SQL within a function

Hello,
I need to create a view on a database based on tables from other databases.
I'm creating a function to lookup the data in the other dbs and use the
function on the view. This is the function.
CREATE FUNCTION CTI_InventoryView ()
RETURNS @.CTI_Inventory TABLE
(INTERID VARCHAR(5), CMPNYNAM VARCHAR(65),
ITEMNMBR VARCHAR(31), ITEMDESC VARCHAR(101),
LOCNCODE VARCHAR(11), RCRDTYPE SMALLINT,
PRIMVNDR VARCHAR(15), LSORDQTY NUMERIC(19,5),
LRCPTQTY NUMERIC(19,5), LSTORDDT DATETIME,
LSORDVND VARCHAR(15), LSRCPTDT DATETIME,
QTYRQSTN NUMERIC(19,5), QTYONORD NUMERIC(19,5),
QTYBKORD NUMERIC(19,5), QTY_Drop_Shipped NUMERIC(19,5),
QTYINUSE NUMERIC(19,5), QTYINSVC NUMERIC(19,5),
QTYRTRND NUMERIC(19,5), QTYDMGED NUMERIC(19,5),
QTYONHND NUMERIC(19,5), ATYALLOC NUMERIC(19,5),
QTYAVAIL NUMERIC(19,5), QTYCOMTD NUMERIC(19,5),
QTYSOLD NUMERIC(19,5))
AS
BEGIN
DECLARE
@.INTERID VARCHAR(5),
@.COMPANYNAME VARCHAR(65),
@.SQL NVARCHAR(1000)
DECLARE CTI_Companies CURSOR FOR
SELECT INTERID, CMPNYNAM
FROM DYNAMICS..SY01500
OPEN CTI_Companies
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'USE ' + @.INTERID + 'SELECT ' + ''''+ @.InterID + '''' + ', '
+ '''' + @.CompanyName + ''''
SELECT @.SQL = @.SQL + 'IV00102.ITEMNMBR, ITEMDESC, IV00102.LOCNCODE,
RCRDTYPE, PRIMVNDR, '
SELECT @.SQL = @.SQL + 'LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT,
QTYRQSTN, QTYONORD, QTYBKORD, '
SELECT @.SQL = @.SQL + 'QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND,
QTYDMGED, QTYONHND, ATYALLOC, '
SELECT @.SQL = @.SQL + 'QTYONHND - ATYALLOC, QTYCOMTD, QTYSOLD '
SELECT @.SQL = @.SQL + 'FROM IV00102 JOIN IV00101 ON IV00102.ITEMNMBR =
IV00101.ITEMNMBR '
INSERT INTO @.CTI_Inventory
EXECUTE sp_executesql @.SQL
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
END
CLOSE CTI_Companies
DEALLOCATE CTI_Companies
RETURN
END
GO
I found that I cannot call the sp_executesql stored procedure inside the
function. I receive this error
Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
within a function.
I'm running out of ideas to do this, any ideas how to do this?,
Thanks,
Sandra.Sandra Parra (sparra@.citrinetech.com) writes:
> I need to create a view on a database based on tables from other
> databases.
> I'm creating a function to lookup the data in the other dbs and use the
> function on the view. This is the function.
>...
> I found that I cannot call the sp_executesql stored procedure inside the
> function. I receive this error
> Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
> Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
> within a function.
> I'm running out of ideas to do this, any ideas how to do this?,
Why are there so many databases, and why do need a view over all them?
I ask this question to possibly be able to give a better response.
From what I see here, I can think of two ways:
1) The number of databases is so dynamic, that you need a iterate over
all databases for every query. In such case, write a stored procedure
and get data into a temp table.
2) The number of databases is static enough, so you can set up a
job that defines a view every night, so that run can run queries
on this view during the day.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx