query that will return the information of every table in a database
-
Hi,
I'm seeking for a query that will return the information listed below for every table in a database.
Any suggestions please.
Thank you.
-
This is a start. I would check every section for accuracy, especially the use of sys.sql_expression_dependencies.
WITH Tabs AS
(
SELECT DISTINCT b.[name] AS SchemaName, a.[name] as TableName, a.object_id,
a.create_date AS CreateDate
FROM sys.tables AS a
JOIN sys.schemas AS b ON a.schema_id = b.schema_id
),
Pkeys AS
(
SELECT DISTINCT b.object_id, b.[name] AS Tablename
FROM sys.indexes AS a
INNER JOIN sys.tables AS b ON a.object_id = b.object_id
WHERE a.is_primary_key = 1
),
NonClu AS
(
SELECT DISTINCT b.object_id, b.[name] AS Tablename
FROM sys.indexes AS a
INNER JOIN sys.tables AS b ON a.object_id = b.object_id
WHERE a.[type] = 2
),
Clu AS
(
SELECT DISTINCT b.object_id, b.[name] AS Tablename
FROMsys.indexes AS a
INNERJOIN sys.tables AS b ON a.object_id = b.object_id
WHEREa.[type] = 1
),
fKey AS
(
SELECT DISTINCT a.[Name] AS TableName, a.object_id
FROM sys.Tables AS a
JOIN sys.Foreign_Keys AS b ON a.object_id = b.parent_object_id
),
Deps as
(
SELECT DISTINCT a.[name] AS TableName, a.object_id, c.[type]
FROM sys.objects AS a
JOIN sys.sql_expression_dependencies AS b on a.object_id = b.referenced_id
JOIN sys.objects AS c on b.referencing_id = c.object_id
)
SELECT a.*,
IIF(b.object_id IS NULL,0,1) AS HasPkey,
IIF(c.object_id IS NULL,0,1) AS HasNonClu,
IIF(d.object_id IS NULL,0,1) AS HasClu,
IIF(e.object_id IS NULL,0,1) AS HasFKey,
IIF(f.object_id IS NULL,0,1) AS HasProcDepend,
IIF(g.object_id IS NULL,0,1) AS HasViewDepend
FROM Tabs AS a
LEFT OUTER JOIN Pkeys AS b ON a.object_id = b.Object_id
LEFT OUTER JOIN NonClu AS c ON a.object_id = c.Object_id
LEFT OUTER JOIN Clu AS d ON a.object_id = d.Object_id
LEFT OUTER JOIN fKey AS e ON a.object_id = e.object_id
LEFT OUTER JOIN Deps AS f ON a.object_id = f.object_id AND f.[type] = 'P'
LEFT OUTER JOIN Deps AS g ON a.object_id = g.object_id AND g.[type] = 'V';
GO -
Thank you.
-
A slightly different approach
SELECT
CONCAT(SCHEMA_NAME(t.schema_id), '.', t.name) AS TableName
, t.create_date AS CreatedDate
, t.modify_date AS LastModifiedDate
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND is_primary_key = 1) THEN 1 ELSE 0 END AS BIT) AS HasPkey
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND type = 2) THEN 1 ELSE 0 END AS BIT) AS HasNonClu
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id=t.object_id AND type = 1) THEN 1 ELSE 0 END AS BIT) AS HasClu
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.Foreign_Keys WHERE parent_object_id=t.object_id) THEN 1 ELSE 0 END AS BIT) AS HasFKey
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type IN ('P','PC','X') WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasProcDep
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type IN ('FN','FS','FT','IF','TF') WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasFuncDep
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.sql_expression_dependencies d JOIN sys.objects o ON o.object_id=d.referencing_id AND o.type = 'V' WHERE d.referenced_id=t.object_id ) THEN 1 ELSE 0 END AS BIT) AS HasViewDep
, CAST(CASE WHEN EXISTS (SELECT * FROM sys.columns c JOIN sys.types ty ON ty.system_type_id=c.system_type_id AND ty.user_type_id=c.user_type_id WHERE c.object_id=t.object_id AND ty.name LIKE '%date%') THEN 1 ELSE 0 END AS BIT) AS HasDateField
FROM sys.tables t -
Thank you.
Is it possible to add ROW_COUNT and HasTrigger to above select statement.?
-
This was removed by the editor as SPAM
-
I'm attempting to utilize this query, but I've discovered that HasViewDep is not producing accurate results. Some of the tables are used in inner view select statements (subqueries or joins), and those tables are returned as '0' for HasViewDep .
Thank you.
- adisql wrote:
I'm attempting to utilize this query, but I've discovered that HasViewDep is not producing accurate results. Some of the tables are used in inner view select statements (subqueries or joins), and those tables are returned as '0' for HasViewDep .
Thank you.
Can you provide examples of this pattern?
-
Please note that if the the view references a table through a synonym the direct reference from the table will not be there. Maybe this is your use case?
-
TableB is utilized in the view below, but HasViewDep for that table displays 0.
CREATE VIEW [xxx].[xxxxxxxxxxx]
AS
SELECT xxxxxx,xxxxxx,xxxxx,xxx
FROM TableA(NOLOCK) co
WHERE EXISTS ( SELECT 1
FROM TableB AS a
WHERE ( xxxx = 'P' ) AND
a.xxxx = co.xxxxxxxx )
-
Stange! My tests don't show this behavior. What do you see in SSMS when you look at the dependencies? Do the view show as a reference from TableB?
-
Also, if you run this:
SELECT
o1.Name as ReferencingObject,
o1.type_desc AS ReferencingObjectType,
d.referenced_server_name,
d.referenced_database_name,
d.referenced_schema_name,
d.referenced_entity_name,
o2.name AS ReferencedObject,
o2.type_desc AS ReferencedObjectType
FROM sys.sql_expression_dependencies d
JOIN sys.objects o1 ON o1.object_id=d.referencing_id
LEFT JOIN sys.objects o2 ON d.referenced_id=o2.object_id
WHERE o1.name='yourView'Do you see a reference to both tables? If yes, are there obvious differences between them?
-
I could see as attached.
- adisql wrote:
I could see as attached.
Ahh, okay. I think you'll have to comment on that image, because there isn't much to see after you have redacted most of the information. 🙂
There seems to be several references to both tables and functions, including some nesting. So not as straight-forward as your example query.
Which one is your "tableB"?
-
Please find attached.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply