May 24, 2018 at 9:32 am
Hi Everyone,
I have a cursor that I want to run through our server, get a list of the DB's then insert all the tables and views and view definitions into a table. my cursor runs but doesn't show results in the query. however the PRINT statement show the entire list of DB's.
could someone point me in the right direction?
Thank you in advance!
Larry
Set NOCOUNT ON;
Declare @DBName varchar(max);
Declare @info Table (DBName varchar(max), TableSchema varchar(4),TableName varchar(50),Defintion varchar(max))
Declare @strSQL nvarchar(max);
Set @strSQL = 'SELECT
db.*
, viewDef.definition as viewDef
FROM ' + '['+ @DBName + '].' + ' INFORMATION_SCHEMA.TABLES db
Left Join
(
SELECT
v.name, m.definition
FROM' + '['+ @DBName + '].' + ' sys.views v
INNER JOIN' + '[' + @DBName + '].' + ' sys.sql_modules m ON m.object_id = v.object_id
) viewDef On viewDef.name = db.TABLE_NAME'
Declare DBName_Cursor CURSOR Fast_Forward for
---Get list of DB names
Select name From sys.databases
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor
--INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM DBName_Cursor
Into @DBName;
--Insert Into @info (DBName)
--Select @DBName
Insert Into @info (DBName,TableSchema, TableName,Defintion)
EXEC sp_executesql @strSQL
Print @DBName
-----Get list of Table names and views for DB
--SELECT
-- db.*, viewDef.definition as viewDef
--FROM
-- @DBName + '.' + INFORMATION_SCHEMA.TABLES db
-- Left Join
-- (
-- SELECT
-- v.name, m.definition
-- FROM @DBName + '.' +sys.views v
-- INNER JOIN @DBName + '.' +sys.sql_modules m ON m.object_id = v.object_id
-- ) viewDef On viewDef.name = db.TABLE_NAME
END
Select * From @info
Close DBName_Cursor;
Deallocate DBName_Cursor;
Go
This is the strSQL I took out to test... it works.
Declare @strSQL nvarchar(max);
Declare @DBName varchar(70);
Set @DBName = [your DB name]
Set @strSQL = 'SELECT
db.*
--, viewDef.definition as viewDef
FROM ' + '['+ @DBName + '].' + ' INFORMATION_SCHEMA.TABLES db
Left Join
(
SELECT
v.name, m.definition
FROM' + '['+ @DBName + '].' + ' sys.views v
INNER JOIN' + '[' + @DBName + '].' + ' sys.sql_modules m ON m.object_id = v.object_id
) viewDef On viewDef.name = db.TABLE_NAME'
Exec sp_executesql @strSQL
May 24, 2018 at 9:53 am
That might be because you're not changing your dynamic code on every iteration, so you're just trying to run a null value.
Set NOCOUNT ON;
Declare @DBName sysname;
Declare @info Table (DBName sysname, TableSchema sysname,TableName sysname,Defintion varchar(max))
Declare @strSQL nvarchar(max);
Declare DBName_Cursor CURSOR Fast_Forward for
---Get list of DB names
Select name From sys.databases
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = REPLACE( 'SELECT ''<<db_name>>'', s.name, o.name, sm.definition ' + CHAR(10) +
'FROM <<db_name>>.sys.objects AS o' + CHAR(10) +
'JOIN <<db_name>>.sys.schemas AS s ON o.schema_id = s.schema_id' + CHAR(10) +
'LEFT JOIN <<db_name>>.sys.sql_modules AS sm ON o.object_id = sm.object_id' + CHAR(10) +
'WHERE o.type IN (''U'', ''V'');', '<<db_name>>', @DBName)
Insert Into @info (DBName,TableSchema, TableName,Defintion)
EXEC sp_executesql @strSQL
Print @strSQL
FETCH NEXT FROM DBName_Cursor
Into @DBName;
END
Select * From @info
Close DBName_Cursor;
Deallocate DBName_Cursor;
GO
EDIT: I changed the code to make it simpler using only system views.
May 24, 2018 at 10:36 am
Just another version:
SET NOCOUNT ON;
DECLARE @SQLCmd NVARCHAR(MAX)
, @SQLCmdTemplate NVARCHAR(MAX)
, @DBName NVARCHAR(128);
IF OBJECT_ID('tempdb..#Info') IS NOT NULL
DROP TABLE #Info;
CREATE TABLE #Info (
[DatabaseName] sysname NOT NULL
, [ObjectSchema] sysname NOT NULL
, [ObjectName] sysname NOT NULL
, [ObjectType] sysname NOT NULL
, [Definition] NVARCHAR(MAX) NULL);
SET @SQLCmdTemplate = N'
USE [!DBName!];
SELECT
DB_NAME() AS [DatabaseName]
, OBJECT_SCHEMA_NAME([obj].[object_id]) AS [ObjectSchema]
, [obj].[name] AS [ObjectName]
, [obj].[type_desc] AS [ObjectType]
, [sm].[definition] AS [Definition]
FROM
[sys].[objects] AS [obj]
LEFT OUTER JOIN [sys].[sql_modules] AS [sm]
ON [sm].[object_id] = [obj].[object_id]
WHERE
[obj].[type] IN (''U'',''V'');
'
DECLARE [DatabaseCursor] CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
[d].[name]
FROM
[sys].[databases] AS [d]
WHERE
[d].[database_id] > 4
ORDER BY
[d].[name];
OPEN [DatabaseCursor];
FETCH NEXT FROM [DatabaseCursor]
INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCmd = REPLACE(@SQLCmdTemplate,'!DBName!',@DBName);
INSERT INTO #Info([DatabaseName], [ObjectSchema], [ObjectName], [ObjectType], [Definition])
EXEC [sys].[sp_executesql] @stmt = @SQLCmd;
FETCH NEXT FROM [DatabaseCursor]
INTO @DBName;
END
CLOSE [DatabaseCursor];
DEALLOCATE [DatabaseCursor];
SET NOCOUNT OFF;
SELECT
[DatabaseName]
, [ObjectSchema]
, [ObjectName]
, [ObjectType]
, [Definition]
FROM
#Info
ORDER BY
[DatabaseName]
, [ObjectType]
, [ObjectSchema]
, [ObjectName];
May 24, 2018 at 10:41 am
You guys are super fantastic!! Now the issue is permissions for the schema sys. it works if I do it individually but there's over 200 DB and countless tables and views.
I'm working with the system admin to get correct permissions.
Thank you so much!
Larry
May 24, 2018 at 10:47 am
larrycook74 - Thursday, May 24, 2018 10:41 AMYou guys are super fantastic!! Now the issue is permissions for the schema sys. it works if I do it individually but there's over 200 DB and countless tables and views.I'm working with the system admin to get correct permissions.
Thank you so much!
Larry
Glad to hear. Some of us seem to write a fair amount of dynamic SQL like this. Cursors are a tool, and like any tool, can be used for good or evil. This is one of those times it is good. Although there are those that would still do this without a cursor, and yes it can.
May 24, 2018 at 10:56 am
One more question: Can you think of any reason why the strSQL portion when run (testing) worked with NO "permission to schema sys" issues, but when the cursor query runs, I see a list of DB's but with the one line error and the @info table doesn't show?
I've run this up the chain to the system admin, but figure I'd throw it out here too. 🙂
Larry
May 24, 2018 at 1:14 pm
larrycook74 - Thursday, May 24, 2018 10:56 AMOne more question: Can you think of any reason why the strSQL portion when run (testing) worked with NO "permission to schema sys" issues, but when the cursor query runs, I see a list of DB's but with the one line error and the @info table doesn't show?I've run this up the chain to the system admin, but figure I'd throw it out here too. 🙂
Larry
Would help to see the full error message you are getting.
May 24, 2018 at 1:20 pm
sorry...should've shared (slaps forehead)
Msg 229, level 14, State 5, Line 1
The SELECT permission was denied on the object 'sql_modules',database 'mssqlsystemresource',schema 'sys'
but it only happened on 3 tables.
Larry
May 24, 2018 at 1:29 pm
All that you would need is SELECT permissions on the sys schema. If they want it more fine grained then select permissions on sys,objects and sys,sql_modules.
May 24, 2018 at 1:33 pm
That's because it wasn't running anything inside the cursor to reference all the databases. With the changes, it tried to read from the databases and the permissions came into effect.
May 24, 2018 at 1:49 pm
Thank you both!!! 😀
Have a great weekend!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply