Archive for the ‘SQL Server’ Category
Wednesday, January 14th, 2009
How do you use it as BI Documentor?
SQL Documentor can be used to generate Lineage Documentation for your databawarehouse and Business intelligence reporting.
You can add your own SQL queries within SQL Documentor and then link it to detail or child level queries. This in turn can be drilled into another ...
Posted in SQL Server | No Comments »
Tuesday, December 30th, 2008
Overview: SQL Documentor for SQL server is a modest PDF and HTML document generator for your schema objects. You can document your tables, views, procedures, triggers, indexes with few clicks. It has a pre-built dashboard interface for easy browsing of your meta-data.
Impress your clients by providing them with pixel perfect ...
Posted in SQL Server | No Comments »
Friday, December 5th, 2008
Here is a sneak peek at the new SQL Server Documentation tool. This tool is extremely customizable by the end user. You can add your own meta-data queries, change existing queries, change the color formatting and has multiple uses.
The interface is quite easy to understand.
Connection Wizard
In this window, you provide ...
Posted in SQL Server | No Comments »
Wednesday, November 12th, 2008
There are two ways to get the table size
Run the stored procedure
exec sp_spaceused "Sales.ContactCreditCard"
Now what if you needed to get a list of Top 20 Tables by number of rows or disk space?
Use the following query
SELECT t.schema_name+ ' - '+ t.table_name as schema_table
, t.index_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
, ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies check constraints
CREATE VIEW INFORMATION_SCHEMA.CHECK_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(schema_id) AS CONSTRAINT_SCHEMA,
name AS CONSTRAINT_NAME,
convert(nvarchar(4000), definition) AS CHECK_CLAUSE
FROM
sys.check_constraints
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Displays columns accessable to the current user
CREATE VIEW INFORMATION_SCHEMA.COLUMNS
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
ColumnProperty(c.object_id, c.name, ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies privileges granted to or by current user
CREATE VIEW INFORMATION_SCHEMA.COLUMN_PRIVILEGES
AS
SELECT
user_name(p.grantor_principal_id) AS GRANTOR,
user_name(p.grantee_principal_id) AS GRANTEE,
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
convert(varchar(10),
CASE p.type
WHEN 'SL' THEN 'SELECT'
WHEN 'UP' THEN 'UPDATE'
WHEN 'RF' THEN 'REFERENCES'
END) AS PRIVILEGE_TYPE,
convert(varchar(3),
CASE p.state
WHEN 'G' THEN 'NO'
WHEN 'W' THEN 'YES'
END) ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies tables AND columns that have constraints
CREATE VIEW INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
AS
SELECT
KCU.TABLE_CATALOG,
KCU.TABLE_SCHEMA,
KCU.TABLE_NAME,
KCU.COLUMN_NAME,
KCU.CONSTRAINT_CATALOG,
KCU.CONSTRAINT_SCHEMA,
KCU.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
UNION ALL
SELECT
db_name() AS TABLE_CATALOG,
schema_name(u.schema_id) AS TABLE_SCHEMA,
u.name AS TABLE_NAME,
col_name(d.referenced_major_id, d.referenced_minor_id)
AS COLUMN_NAME,
db_name() AS CONSTRAINT_CATALOG,
schema_name(k.schema_id) AS CONSTRAINT_SCHEMA,
k.name AS CONSTRAINT_NAME
FROM
sys.check_constraints k JOIN sys.objects u ON u.object_id = k.parent_object_id
JOIN ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies user defined datatype that have constraints
CREATE VIEW INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
AS
SELECT
db_name() AS CONSTRAINT_CATALOG,
schema_name(o.schema_id) AS CONSTRAINT_SCHEMA,
o.name AS CONSTRAINT_NAME,
db_name() AS DOMAIN_CATALOG,
schema_name(t.schema_id) AS DOMAIN_SCHEMA,
t.name AS DOMAIN_NAME,
'NO' AS IS_DEFERRABLE,
'NO' AS INITIALLY_DEFERRED
FROM
sys.types t
JOIN sys.objects o ON o.object_id = t.rule_object_id
WHERE
t.user_type_id > ...
Posted in SQL Server | No Comments »
Tuesday, October 21st, 2008
-- Identifies tables that have constraints
CREATE VIEW INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(t.schema_id) AS TABLE_SCHEMA,
t.name AS TABLE_NAME,
db_name() AS CONSTRAINT_CATALOG,
schema_name(c.schema_id) AS CONSTRAINT_SCHEMA,
c.name AS CONSTRAINT_NAME
FROM
sys.objects c JOIN sys.tables t
ON t.object_id = c.parent_object_id
WHERE
c.type IN ('C' ,'UQ' ,'PK' ,'F')
--------
Posted in SQL Server | No Comments »